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.
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.
FIRST_VALUE(ColName) OVER(PARTITION BY ColName ORDER BY ColName)
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.
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.
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.
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.
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.
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.
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.