First Value and Last Value Functions in SQL Server

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.

Let’s have a look at how it actually works.

Problem Scenario:
The following is a sort of scoreboard where each person has their own set of points. To know where they stand, each row must have a low and high score associated with it.

Dataset:
ID, Name, Score
1011, ‘Scott’, 2100
1012, ‘Peter’, 2220
1013, ‘John’, 2010
1014, ‘George’, 2009
1015, ‘Thomos’, 2500
1016, ‘Veronica’, 2110
1017, ‘Anthony’, 2011

Required Output is-
ID, Name, Score, LowestScore, HighestScore
1011, ‘Scott’, 2100, 2009,2500
1012, ‘Peter’, 2220, 2009,2500
1013, ‘John’, 2010, 2009,2500
1014, ‘George’, 2009, 2009,2500
1015, ‘Thomos’, 2500, 2009,2500
1016, ‘Veronica’, 2110, 2009,2500
1017, ‘Anthony’, 2011, 2009,2500

Now, let use First_Value and Last_Value functions.

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;

firstvalue_lastvalue_sqlserver

Note:
partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.

order_by_clause determines the order of the data before the function is applied. The order_by_clause is required. rows_range_clause further limits the rows within the partition by specifying start and end points.

I hope you found this post to be informative.

To receive notifications of new posts, please subscribe.