SQL · Window Functions

FIRST_VALUE and LAST_VALUE

Mediumsql-22

first_valuelast_valueframe

Question

How do FIRST_VALUE and LAST_VALUE work?

FIRST_VALUE() returns the first value within the window.

LAST_VALUE() returns the last value within the window frame.

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

So:

LAST_VALUE(salary) OVER (
    ORDER BY salary
)

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

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

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

🎯 Put this concept into practice

Solidify this answer with real hands-on interview drills in the browser studio.