First_Value, Last_Value vs Min, Max in SQL Server

The MIN and MAX functions in SQL Server can be used separately on a column level to find the lowest and maximum value of a column. The same functions can be used in an analytical context to extract the lowest and highest values from an ordered partition of a result set.

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.

The First_Value and Last_Value functions in SQL Server have a disadvantage over Oracle SQL and Apache Impala in that they do not ignore NULL values. IGNORE NULLS and RESPECT NULLS currently works only in Azure SQL Edge. So, this makes it difficult to use the functions in situations where there are multiple NULL values because they don’t handle NULLs gracefully. We’ll have to rely on some workarounds there.

MIN and MAX functions, on the other hand, are significantly better, especially if the column values contain any nulls.

However, performance-wise, FIRST_VALUE and LAST_VALUE are much better than MIN and MAX.

Let’s run some tests to see what the differences are.

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

Code:

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),
(1018, ‘Sham’, NULL)

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,
MIN(iScore)
OVER (ORDER BY iScore DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as LowestiScore,
MAX(iScore)
OVER (ORDER BY iScore DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as HighestiScore
FROM tblEmpScores;

Result:
firstvalue_lastvalue_vs_min_max_sqlserver_NotNull

If you see, both the queries returned the same result. Let’s check the execution plans.

This slideshow requires JavaScript.

If you look at the execution plans, the table-scan is higher when MIN and MAX functions are used.

Let’s add one more row into the table with NULL value in the score.
INSERT INTO tblEmpScores VALUES (1018, ‘Sham’, NULL)

firstvalue_lastvalue_vs_min_max_sqlserver

Since there is no support for ‘IGNORE_NULL’ values, the LAST_VALUE function returned NULL instead of the lowest value.

I hope you found this post to be informative.

To receive notifications of new posts, please subscribe.

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s