SQL Server 2012 version has ended a drought of insufficient analytical functions in SQL Server. Analytical functions like LEAD, LAG, FIRST_VALUE and LAST_VALUE that made querying and reporting easy – especially in the Business Intelligence domain. Performing analytical operations before these functions was a tedious task; Writing complex queries needed nested queries and self-joins resulting in poor performance.
Analytical functions are computed on each row instead of working on groups like aggregate functions does – like MIN, MAX, COUNT, SUM. In this tech-recipe post, we’re going to learn two helpful analytical functions FIRST_VALUE and LAST_VALUE.
1.
FIRST_VALUE
Returns the first value in an ordered set of values in a specified column. It’s mandatory to use OVER clause with ORDER BY to have an ordered set. PARTITION BY is optional.
Syntax
FIRST_VALUE(ColName) OVER(PARTITION BY ColName ORDER BY ColName)
2.
LAST_VALUE
Returns the last value in an ordered set of values in a specified column. It’s mandatory to use OVER clause with ORDER BY to have an ordered set. PARTITION BY is optional. Besides, we need to consider window framing while using LAST_VALUE function to avoid unexpected results.
Syntax
LAST_VALUE(ColName) OVER(PARTITION BY ColNameORDER BY ColName ROW_OR_RANGE_Frame)
Window Frame in SQL Server
Understanding windowing and aggregation function in depth requires a good understanding of window framing in SQL Server. As we aware of that window function works on a subset of rows in a partition. Framing helps us to decide the upper boundary & lower boundary for a partition where analytical function work.
Window frames can be indicated as following with ORDER BY clause.
RANGE BETWEEN start_boundary AND end_boundary
ROWS BETWEEN start_boundary AND end_boundary
It’s important to understand the following terms before we use window frames in our queries.
Let’s walk through with few helpful examples using FIRST_VALUE and LAST_VALUE functions.
1.
FIRST_VALUE – Without Partition BY
In the following example, we’re using FIRST_VALUE function to find the very first value in Salary column ordered ascending. While we observed that salary column was not in the order when populated employee table. Applying FIRST_VALUE with ORDER BY causes to order the rows and fetch the first value from the ordered set of values.
use tempdb; DROP TABLE IF EXISTS #Employee; CREATE TABLE #Employee ( Id INT ,Salary INT ); INSERT INTO #Employee VALUES (1, 200), (2, 100), (3, 300), (4, 500), (5, 400); SELECT * FROM #Employee; SELECT Id, Salary, FIRST_VALUE(Salary) OVER(ORDER BY Salary) as FirstValue FROM #Employee;
2.
LAST_VALUE – Without Partition By
Following example displays the last value from an ordered set of values. Make a note of using LAST_VALUE function without window frame produces an incorrect result because when we’ve not specified any window frame then it uses default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW which only goes up to current row hence giving the incorrect row value which results in incorrect values.
Specifying the correct window frame ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING produces a correct result by taking all the rows into consideration before producing the last value.
use tempdb; DROP TABLE IF EXISTS #Employee; CREATE TABLE #Employee ( Id INT ,Salary INT ); INSERT INTO #Employee VALUES (1, 200), (2, 100), (3, 300), (4, 500), (5, 400); SELECT * FROM #Employee; --Produces wrong output because default framing giving the wrong order SELECT Id, Salary, LAST_VALUE(Salary) OVER(ORDER BY Salary) as LastValue FROM #Employee; --Produces correct output when correct window frame is applied SELECT Id, Salary, LAST_VALUE(Salary) OVER(ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as LastValue FROM #Employee;
3.
FIRST_VALUE and PARTITION BY
Running following example, we can observe that result of the query was partitioned by dept name column thus creating two logical windows of IT dept and HR dept. When we have two different windows then FIRST_VALUE function is applied to each partition to fetch first value from the ordered set using salary column in ascending order.
--First Value and Partition By use tempdb; DROP TABLE IF EXISTS #Employee; CREATE TABLE #Employee ( Id INT ,DeptName VARCHAR(25) ,Salary INT ); INSERT INTO #Employee VALUES (1, 'IT', 200), (2, 'IT', 100), (3, 'IT', 300), (4, 'HR', 500), (5, 'HR', 400); SELECT * FROM #Employee; SELECT Id, DeptName, Salary, FIRST_VALUE(Salary) OVER(PARTITION BY DeptName ORDER BY Salary) as FirstValue FROM #Employee ORDER BY Salary, Id;
4.
LAST_VALUE and PARTITION BY
Here is an example using PARTITION BY with LAST_VALUE function. Similar to the above example; the result of query divided into two partitions of I.T dept and HR dept and applying LAST_VALUE function to each partition to fetch last value from an ordered set of values.
We have used ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING window frame to make sure LAST_VALUE function considers rows between first row in partition to last row in partition.
use tempdb; DROP TABLE IF EXISTS #Employee; CREATE TABLE #Employee ( Id INT ,DeptName VARCHAR(25) ,Salary INT ); INSERT INTO #Employee VALUES (1, 'IT', 200), (2, 'IT', 100), (3, 'IT', 300), (4, 'HR', 500), (5, 'HR', 400); SELECT * FROM #Employee; SELECT Id, DeptName, Salary, LAST_VALUE(Salary) OVER(PARTITION BY DeptName ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as LastValue FROM #Employee ORDER BY Salary, Id;
Summary
In this tech-recipes post we have learnt to use FIRST_VALUE and LAST_VALUE function in SQL Server with the help of window frame. If you like this post you may read tech-recipes database archives to read more useful stuff.
