Solve “Deduplicate events with ROW_NUMBER”

Free. Runs in your browser, no install.

SQL · Window Functions

FIRST_VALUE and LAST_VALUE

Mediumfirst_valuelast_valueframe

Interview question

How do FIRST_VALUE and LAST_VALUE work?

How to answer

The short answer

FIRST_VALUE() returns the first value within the window.

LAST_VALUE() returns the last value within the .

The common surprise is that the default frame can end at the current row.

So:

SQL
LAST_VALUE(salary) OVER (
    ORDER BY salary
)

may return the current row's salary rather than the final salary in the entire .

Use an explicit frame when you want the true final value:

SQL
LAST_VALUE(salary) OVER (
    ORDER BY salary
    ROWS BETWEEN UNBOUNDED PRECEDING
         AND UNBOUNDED FOLLOWING
)

Lock it in with a real drill

You remember an answer longer once you have used it. Try a hands-on problem on this concept.

Open related drill