Home > SQLServer, SQLServer 2012 > SQL Server – "Denali" – Analytic Functions FIRST_VALUE() and LAST_VALUE()

SQL Server – "Denali" – Analytic Functions FIRST_VALUE() and LAST_VALUE()


FIRST_VALUE() and LAST_VALUE() are new analytic function introduced in SQL Server "Denali". As the name suggests FIRST_VALUE() returns first value in an ordered set of values, and LAST_VALUE() returns the last value from an ordered set of values.

For example,

SELECT EmployeeID, FirstName, MiddleName, LastName,

       FIRST_VALUE(EmployeeID) OVER (ORDER BY EmployeeID)

       AS [First Value]

FROM   Table_Employees

Result Set:

EmployeeID   FirstName    MiddleName   LastName First Value

1            Ken          J            Sánchez  1

2            Ken          J            Sánchez  1

3            Ken          J            Sánchez  1

4            Terri        Lee          Duffy    1

5            Terri        Lee          Duffy    1

6            Terri        Lee          Duffy    1

7            Terri        Lee          Duffy    1

 

These functions can also be used with PARTITION BY clause, which allows us to group the result set into subsets.

Using PARTITION BY with FIRST_VALUE():

By using PARTITION BY clause with FIRST_VALUE() function we can divide the result set by name:

SELECT EmployeeID, FirstName, MiddleName, LastName,

       FIRST_VALUE(EmployeeID) OVER

       (PARTITION BY FirstName, MiddleName, LastName

       ORDER BY FirstName, MiddleName, LastName)

       AS [First Value]

FROM   Table_Employees

Result Set:

EmployeeID   FirstName    MiddleName   LastName First Value

1            Ken          J            Sánchez  1

2            Ken          J            Sánchez  1

3            Ken          J            Sánchez  1

4            Terri        Lee          Duffy    4

5            Terri        Lee          Duffy    4

6            Terri        Lee          Duffy    4

7            Terri        Lee          Duffy    4

 

Now let’s try to use this to find duplicate rows which we tried yesterday:

;WITH  EmployeesCTE

       (EmployeeID, FirstName, MiddleName, LastName, DuplicateOf)

AS

(

       SELECT EmployeeID, FirstName, MiddleName, LastName,

              FIRST_VALUE(EmployeeID) OVER

              (PARTITION BY FirstName, MiddleName, LastName

              ORDER BY FirstName, MiddleName, LastName)

       FROM   Table_Employees

)

SELECT EmployeeID, FirstName, MiddleName, LastName, DuplicateOf

FROM   EmployeesCTE

WHERE  EmployeeID <> DuplicateOf — Duplicate Rows

       –EmployeeID = DuplicateOf — Unique Rows

Note that we no longer need a self-join in this case.

Result Set:

EmployeeID   FirstName    MiddleName   LastName DuplicateOf

2            Ken          J            Sánchez  1

3            Ken          J            Sánchez  1

5            Terri        Lee          Duffy    4

6            Terri        Lee          Duffy    4

7            Terri        Lee          Duffy    4

LAST_VALUE() has the same syntax and clauses as FIRST_VALUE(), the only difference is, it returns last value from an ordered set:

SELECT EmployeeID, FirstName, MiddleName, LastName,

       FIRST_VALUE(EmployeeID) OVER

       (PARTITION BY FirstName, MiddleName, LastName

       ORDER BY FirstName, MiddleName, LastName)

       AS [FirstValue],

       LAST_VALUE(EmployeeID) OVER

       (PARTITION BY FirstName, MiddleName, LastName

       ORDER BY FirstName, MiddleName, LastName)

       AS [LastValue]

FROM   Table_Employees

Result Set:

EmployeeID FirstName  MiddleName  LastName  FirstValue  LastValue  

1          Ken        J           Sánchez   1           3

2          Ken        J           Sánchez   1           3

3          Ken        J           Sánchez   1           3

4          Terri      Lee         Duffy     4           7

5          Terri      Lee         Duffy     4           7

6          Terri      Lee         Duffy     4           7

7          Terri      Lee         Duffy     4           7

 

Hope This Helps!

Vishal

  1. No comments yet.
  1. No trackbacks yet.