A couple a weeks ago I blogged about a few of the enhancements in the OVER clause, and now I will show you guys a couple of new windowing functions, that goes along with the OVER clause. These new functions are FIRST_VALUE() and LAST_VALUE().

I’ll use the same setup as I did in the OVER clause post:

CREATE TABLE Employees (
    EmployeeId INT IDENTITY PRIMARY KEY,
    Name VARCHAR(50),
    HireDate DATE NOT NULL,
    Salary INT NOT NULL
)
GO

INSERT INTO Employees (Name, HireDate, Salary)
VALUES
    ('Alice', '2011-01-01', 20000),
    ('Brent', '2011-01-15', 19000),
    ('Carlos', '2011-02-01', 22000),
    ('Donna', '2011-03-01', 25000),
    ('Evan', '2011-04-01', 18500)
GO

Last time I found the average salary of the entire group, and also controlled the size of the window frame. Now I would like to know the name, salary and hire date of the person with the higest salary. In the good old days we would solve this by using a mess of subqueries, but now we can do this quite elegantly:

SELECT
    EmployeeId,
    Name,
    Salary,
    HireDate,
    LAST_VALUE(Name) OVER(
                    ORDER BY Salary
                    RANGE BETWEEN UNBOUNDED PRECEDING
                    AND UNBOUNDED FOLLOWING) AS HighestSalaryName,
    LAST_VALUE(Salary) OVER(
                    ORDER BY Salary
                    RANGE BETWEEN UNBOUNDED PRECEDING
                    AND UNBOUNDED FOLLOWING) AS HighestSalary,
    LAST_VALUE(HireDate) OVER(
                    ORDER BY Salary
                    RANGE BETWEEN UNBOUNDED PRECEDING
                    AND UNBOUNDED FOLLOWING) AS HiredateOfHighestSalary
FROM Employees
ORDER BY EmployeeId
GO

This resultset gives us one row per employee, but with the following columns added: HigestSalaryName, HighestSalary and HireDateOfHigestSalary.

The function LAST_VALUE(x) returns the value of column x in the last row of the window frame defined in the OVER clause. I have defined the ORDER BY clause to order by Salary, so the FIRST_VALUE() will look at the row with the lowest salary, and the LAST_VALUE() will look at the row with the higest salary.

The beauty of these functions is, that we can return any of the columns in the dataset given any order by clause. If we think about the MIN(x) and MAX(x) functions, these can only return the value of column x ordered by column x.

Again we can add the new ROWS or RANGE clause to narrow down the window frame, but in the example above I just wanted to look at the entire dataset as one group.