FIRST_VALUE and LAST_VALUE in SQL Server

10b - FIRST_VALUE and LAST_VALUE  (VERY SIMPLE version)
---------------------------------------------------------------------
No setup needed. This file creates its own small table.

FIRST_VALUE = value from the FIRST row of the window
LAST_VALUE  = value from the LAST row of the window
              (needs "ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING")

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

IF DB_ID('SqlLearningDb') IS NULL
    CREATE DATABASE SqlLearningDb;
GO

USE SqlLearningDb;
GO

Small table: runners and their race times (in seconds)

DROP TABLE IF EXISTS dbo.RaceResults;

CREATE TABLE dbo.RaceResults
(
    RunnerName VARCHAR(20),
    RaceName   VARCHAR(10),
    TimeTaken  INT
);

INSERT INTO dbo.RaceResults (RunnerName, RaceName, TimeTaken) VALUES
('Ravi',  '100m', 12),
('Sita',  '100m', 11),
('John',  '100m', 14),
('Arjun', '200m', 25),
('Pooja', '200m', 23),
('Kiran', '200m', 27);
GO

SELECT * FROM dbo.RaceResults;

(6 rows affected)

FIRST_VALUE: the fastest runner (smallest time) shown on every row

SELECT RunnerName, TimeTaken,
       FIRST_VALUE(RunnerName) OVER (ORDER BY TimeTaken) AS Fastest
FROM dbo.RaceResults
WHERE RaceName = '100m';

FIRST_VALUE per race (PARTITION BY)

SELECT RaceName, RunnerName, TimeTaken,
       FIRST_VALUE(RunnerName) OVER (PARTITION BY RaceName ORDER BY TimeTaken) AS RaceWinner
FROM dbo.RaceResults;

LAST_VALUE: the slowest runner

You MUST add the ROWS BETWEEN ... part, otherwise it gives the wrong answer

SELECT RaceName, RunnerName, TimeTaken,
       LAST_VALUE(RunnerName) OVER (PARTITION BY RaceName ORDER BY TimeTaken
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Slowest
FROM dbo.RaceResults;

How many seconds behind the winner?

SELECT RaceName, RunnerName, TimeTaken,
       TimeTaken - FIRST_VALUE(TimeTaken) OVER (PARTITION BY RaceName ORDER BY TimeTaken) AS SecondsBehind
FROM dbo.RaceResults;

Practice Questions

1. Show the WINNING TIME of each race on every row.
2. Remove the ROWS BETWEEN part from query 3 and see the wrong result.