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