FIRST_VALUE and LAST_VALUE in SQL - Step-by-Step Execution

Concept Flow - FIRST_VALUE and LAST_VALUE

Start Query

↓

Partition Data (optional)

↓

Order Rows within Partition

↓

Apply FIRST_VALUE/LAST_VALUE Window Function

↓

Return Result Set with New Columns

↓

End Query

The query starts by optionally partitioning data, then ordering rows, applying FIRST_VALUE or LAST_VALUE to get first or last values in the window, and returns the result.

Execution Sample

SQL

SELECT
  department,
  employee,
  salary,
  FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_salary,
  LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_salary
FROM employees;

This query shows the highest and lowest salary per department using FIRST_VALUE and LAST_VALUE window functions.

Execution Table

Step	Row (department, employee, salary)	Window Order	FIRST_VALUE(salary)	LAST_VALUE(salary)	Notes
1	(Sales, Alice, 9000)	9000, 7000, 6000	9000	6000	First row in Sales partition, highest salary is 9000, last salary is 6000
2	(Sales, Bob, 7000)	9000, 7000, 6000	9000	6000	Second row, FIRST_VALUE stays 9000, LAST_VALUE includes full window with frame clause
3	(Sales, Carol, 6000)	9000, 7000, 6000	9000	6000	Third row, same FIRST_VALUE and LAST_VALUE
4	(HR, Dave, 8000)	8000, 7500	8000	7500	First row in HR, highest salary 8000, lowest 7500
5	(HR, Eve, 7500)	8000, 7500	8000	7500	Second row, values remain same
6	End of rows				Query completes after all rows processed

💡 All rows processed, window functions applied per partition and order.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5	Final
department	N/A	Sales	Sales	Sales	HR	HR	N/A
employee	N/A	Alice	Bob	Carol	Dave	Eve	N/A
salary	N/A	9000	7000	6000	8000	7500	N/A
FIRST_VALUE(salary)	N/A	9000	9000	9000	8000	8000	N/A
LAST_VALUE(salary)	N/A	6000	6000	6000	7500	7500	N/A

Key Moments - 2 Insights

Why does LAST_VALUE need the frame clause 'ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING' to return the expected lowest salary?

Without the frame clause, LAST_VALUE defaults to the frame from the start of the partition to the current row, which can cause it to return the current row's value instead of the last in the full partition. The frame clause ensures the window covers all rows.

Why does FIRST_VALUE always return the same highest salary for all rows in the partition?

Because FIRST_VALUE returns the first value in the ordered window, and since the order is descending by salary, the first value is the highest salary, which stays the same for all rows in that partition.

Visual Quiz - 3 Questions

Look at the execution table, what is the FIRST_VALUE(salary) for the second row in the Sales department?

A 6000

B 7000

C 9000

D NULL

💡 Hint

Check the row 2 in the execution_table under FIRST_VALUE(salary)

At which step does the LAST_VALUE(salary) for HR department first appear as 7500?

A Step 4

B Step 5

C Step 3

D Step 2

💡 Hint

Look at the LAST_VALUE(salary) column for HR rows in execution_table

If we remove the frame clause from LAST_VALUE, what would likely happen to the LAST_VALUE(salary) output?

A It would always show the lowest salary

B It would show the salary of the current row

C It would cause a syntax error

D It would show NULL

💡 Hint

Refer to key_moments about the importance of the frame clause for LAST_VALUE

Concept Snapshot

FIRST_VALUE and LAST_VALUE are window functions.
They return the first or last value in an ordered window.
Use PARTITION BY to group rows.
Use ORDER BY to define order.
LAST_VALUE often needs a frame clause to cover full partition.
They help find first/last values without aggregation.

Full Transcript

This visual execution trace shows how FIRST_VALUE and LAST_VALUE window functions work in SQL. The query partitions employees by department and orders them by salary descending. FIRST_VALUE returns the highest salary in each department for every row. LAST_VALUE returns the lowest salary, but requires a frame clause to cover the entire partition; otherwise, it may return the current row's salary. The execution table walks through each row, showing how these values are computed and remain consistent across rows in the same partition. Key moments clarify why the frame clause is needed for LAST_VALUE and why FIRST_VALUE stays constant. The quiz tests understanding of these behaviors by referencing specific steps and values in the execution table. The snapshot summarizes the syntax and key rules for these window functions.