How To Use FIRST_VALUE & LAST_VALUE Function In SQL Server

Posted December 15, 2018 by Vishwanath Dalvi in Database, SQL Server

How To Refresh Intellisense Cache In SQL Server

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)

FIRST_VALUE Function In SQL server - Tech-Recipes

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)

LAST_VALUE Function In SQL server - Tech-Recipes

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.

Window Frame In SQL server - Tech-Recipes

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;

FIRST_VALUE Function In SQL server_1- Tech-Recipes

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;


LAST_VALUE Function In SQL server_2 - Tech-Recipes

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;


FIRST_VALUE Function Partition By In SQL server - Tech-Recipes

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;


LAST_VALUE Function With Partition By In SQL server - Tech-Recipes

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.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

Leave a Reply