Skip to content
SQLSimplified
window

FIRST_VALUE() and LAST_VALUE()

Show each employee alongside the highest- and lowest-paid peer in their department.

Overview

FIRST_VALUE(col) and LAST_VALUE(col) return the first and last value of col within the window frame. They're handy for comparing each row to the extremes of its group, e.g., each employee vs their department's top and bottom earner. Note the default frame: you usually must extend it to the partition end for a true "last".

The Query

Loading playground environment...

Step-by-Step Breakdown

  1. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: expands the frame to the whole partition so FIRST/LAST see all peers, not just up to the current row.
  2. FIRST_VALUE(salary): the department's highest salary (top of the ORDER BY salary DESC).
  3. LAST_VALUE(salary): the department's lowest salary.

LAST_VALUE default frame trap

By default LAST_VALUE only sees rows up to the current one, so it often returns the current row itself. Always widen the frame for a true partition end.

Variations

Compare each movie to the highest-budget film in its genre:

Loading playground environment...

Common Mistakes

  • Forgetting to widen the frame for LAST_VALUE. The default frame makes LAST_VALUE return the current row.
  • Confusing order direction. "First" and "last" depend entirely on the ORDER BY inside the window.
  • Performance. Explicit unbounded frames are fine, but very large partitions with wide frames can be costly.

Related examples

DENSE_RANK(): Ranking Without Gaps
window
Rank employees by salary company-wide using DENSE_RANK, which never skips numbers after ties.
ROW_NUMBER(): Enumerating Rows Within Groups
window
Assign a sequential number to each employee within their department, ordered by salary.
Window Functions: RANK() and PARTITION BY
window
Rank employees by salary within their own department using RANK() OVER (PARTITION BY ...), without collapsing rows the way GROUP BY does.

Cite this resource

SQLSimplified. "FIRST_VALUE() and LAST_VALUE() Example". Available at: https://sqlsimplified.online/examples/first-value-last-value