The First_Value function in SQL Server returns the first value in an ordered set of values and the Last_Value function returns the last value in an ordered set of values. These two functions are classified as analytical functions since they return the first and last values from a result set’s ordered partition. This implies that these functions must be used in conjunction with the OVER() clause. Please see this page for a thorough explanation of SQL Server’s First Value and Last Value functions.
On the other hand, the LAG function retrieves data from a previous column, whereas the LEAD function retrieves data from the next line. Because the two functions are fundamentally the same, you can simply swap one for the other by modifying the sort request.
Example:
CREATE TABLE tblEmpScores(IdCol INT, vcName VARCHAR(20), iScore INT);
INSERT INTO tblEmpScores VALUES
(1011, ‘Scott’, 2100),
(1012, ‘Peter’, 2220),
(1013, ‘John’, 2010),
(1014, ‘George’, 2009),
(1015, ‘Thomos’, 2500),
(1016, ‘Veronica’, 2110),
(1017, ‘Anthony’, 2011)
SELECT IdCol, vcName, iScore,
LAST_VALUE(iScore)
OVER (ORDER BY iScore DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as LowestiScore,
FIRST_VALUE(iScore)
OVER (ORDER BY iScore DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as HighestiScore
FROM tblEmpScores;
SELECT IdCol, vcName, iScore,
LEAD(iScore,1)
OVER (ORDER BY iScore DESC) as LowestiScore,
LAG(iScore,1)
OVER (ORDER BY iScore DESC) as HighestiScore
FROM tblEmpScores;
Each row has a low and high value associated with it when utilizing the FIRST VALUE and LAST VALUE functions.
Each row has a next and previous value associated with it when utilizing the LEAD and LAG functions.
Please note that the functions ‘LEAD’ and ‘LAG’ functions do not have a window frame hence it cannot be used with UNBOUNDED PRECEDING…FOLLOWING clauses.
I hope you found this post to be informative.
To receive notifications of new posts, please subscribe.
Big Data and SQL 