FIRST_VALUE and LAST_VALUE in SQL

Reaching for the edges of a partition without a second query.

SELECT
    country,
    name,
    score,
    FIRST_VALUE(name) OVER whole_country AS best_in_country,
    LAST_VALUE(name) OVER whole_country AS worst_in_country,
    NTH_VALUE(name, 2) OVER whole_country AS runner_up
FROM players
WINDOW whole_country AS (
    PARTITION BY country
    ORDER BY score DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

How it works

1. FIRST_VALUE reads the first row of the frame, not of the partition.
2. LAST_VALUE needs an explicit frame or it just returns the current row.
3. NTH_VALUE takes any position you can count to.

Keywords and builtins used here

• AND
• AS
• BETWEEN
• BY
• DESC
• FROM
• ORDER
• ROWS
• SELECT

The run, in numbers

Lines

13

Characters to type

333

Tokens

57

Three-star pace

95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 36 seconds.

Type this snippet

Step 7 of 9 in Window functions, step 13 of 24 in Analytics & reporting.

← Previous Next →