Accessing values from the next row
With the LEAD()
function, you can access data from a subsequent row in the same query, without using the GROUP BY
statement. This way, you can easily compare values from an ordered list.
This is the syntax:
LEAD(numeric_expression) OVER ([PARTITION BY column] ORDER BY column)
In this exercise, you will get familiar with comparing values from the current row with values from the following row.
You will select information about the voters from France and arrange the results by total votes, in ascending order. The purpose is to analyze how the number of votes from each voter compares to the number of votes recorded for the next person in the list.
This exercise is part of the course
Functions for Manipulating Data in SQL Server
Exercise instructions
- Create a new column, showing the number of votes recorded for the next person in the list.
- Create a new column with the difference between the current voter's
total_votes
and the votes of the next person.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
first_name,
last_name,
total_votes AS votes,
-- Select the number of votes of the next voter
___(___) OVER (___ total_votes) AS votes_next_voter,
-- Calculate the difference between the number of votes
LEAD(___) ___ (ORDER BY ___) - total_votes AS votes_diff
FROM voters
WHERE country = 'France'
ORDER BY total_votes;