FIRST_VALUE and LAST_VALUE in SQL - Deep Dive

Overview - FIRST_VALUE and LAST_VALUE

What is it?

FIRST_VALUE and LAST_VALUE are SQL functions that help you find the first or last value in a set of rows within a group. They look at a specific order you define and pick the value from the start or end of that order. These functions are often used with window functions to analyze data without collapsing rows.

Why it matters

Without FIRST_VALUE and LAST_VALUE, finding the first or last item in a group would require complex queries or multiple steps. These functions simplify queries, making it easier to get meaningful insights like the earliest or latest event for each category. This saves time and reduces errors in data analysis.

Where it fits

Before learning these functions, you should understand basic SQL SELECT queries, ORDER BY, and window functions like ROW_NUMBER or RANK. After mastering FIRST_VALUE and LAST_VALUE, you can explore more advanced window functions and analytics, such as LEAD, LAG, and aggregate window functions.

Mental Model

Core Idea

FIRST_VALUE and LAST_VALUE pick the first or last item from a sorted group of rows without removing any rows.

Think of it like...

Imagine a line of people waiting for a bus. FIRST_VALUE is like asking who is at the front of the line, and LAST_VALUE is like asking who is at the back, without moving anyone.

Group of rows sorted by a column:

┌─────────┬─────────┐
│ Value   │ Order   │
├─────────┼─────────┤
│ A       │ 1       │ ← FIRST_VALUE picks this
│ B       │ 2       │
│ C       │ 3       │ ← LAST_VALUE picks this
└─────────┴─────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Window Functions

Concept: Window functions perform calculations across a set of rows related to the current row without collapsing the result.

In SQL, window functions let you look at a group of rows around the current row. For example, ROW_NUMBER() assigns a number to each row in a group. This helps us analyze data while keeping all rows visible.

Result

You get a new column showing row numbers for each group, but all original rows remain.

Understanding window functions is key because FIRST_VALUE and LAST_VALUE work within these groups without losing any rows.

2

Foundation

Sorting Rows Within Groups

Concept: Ordering rows inside each group defines which row is first or last.

When using window functions, you specify ORDER BY to sort rows. This order decides which row is considered first or last in that group.

Result

Rows are numbered or processed in the order you define, affecting which values FIRST_VALUE or LAST_VALUE will pick.

Knowing how ORDER BY affects row order is essential to control what FIRST_VALUE and LAST_VALUE return.

3

Intermediate

Using FIRST_VALUE Function

🤔Before reading on: do you think FIRST_VALUE returns the first row overall or the first row per group? Commit to your answer.

Concept: FIRST_VALUE returns the first value in the ordered set of rows for each group defined by the window.

Example: SELECT department, employee, salary, FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_salary FROM employees; This query finds the highest salary per department and shows it on every row in that department.

Result

Each row shows the highest salary in its department, repeated for all employees in that department.

FIRST_VALUE lets you see the first value in a group alongside all rows, enabling easy comparison without extra joins.

4

Intermediate

Using LAST_VALUE Function and Frame Clause

🤔Before reading on: do you think LAST_VALUE always returns the last row in the entire group regardless of frame? Commit to your answer.

Concept: LAST_VALUE returns the last value in the current window frame, which can be controlled to get the true last value in the group.

Example: SELECT department, employee, salary, LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_salary FROM employees; Without the frame clause, LAST_VALUE might return unexpected results because it defaults to the current row to end frame.

Result

Each row shows the lowest salary in its department correctly when the frame covers all rows.

Understanding window frames is crucial for LAST_VALUE to work as expected; otherwise, it may return the current row's value instead of the last.

5

Intermediate

Comparing FIRST_VALUE and LAST_VALUE

🤔Before reading on: do you think FIRST_VALUE and LAST_VALUE behave exactly the same without frame clauses? Commit to your answer.

Concept: FIRST_VALUE always returns the first row in the partition, but LAST_VALUE depends on the window frame and may need adjustment.

By default, FIRST_VALUE looks from the start to the current row, so it always returns the first. LAST_VALUE looks from the start to the current row by default, so it returns the current row's value unless the frame is extended.

Result

FIRST_VALUE is straightforward; LAST_VALUE requires careful frame definition to get the true last value.

Knowing this difference prevents bugs where LAST_VALUE returns unexpected results.

6

Advanced

Optimizing Queries with FIRST_VALUE and LAST_VALUE

🤔Before reading on: do you think using FIRST_VALUE and LAST_VALUE is always faster than subqueries? Commit to your answer.

Concept: Using these functions can simplify queries and improve performance by avoiding joins or subqueries, but improper use can hurt performance.

Example: Instead of joining a table to get the earliest date per group, use FIRST_VALUE with proper partitioning and ordering. However, large datasets with complex partitions may need indexing or query tuning.

Result

Cleaner queries with potentially better performance when used correctly.

Understanding when and how to use these functions helps write efficient, readable SQL.

7

Expert

Handling NULLs and Frame Boundaries in LAST_VALUE

🤔Before reading on: do you think LAST_VALUE ignores NULLs by default? Commit to your answer.

Concept: LAST_VALUE includes NULLs and depends on frame boundaries, which can cause subtle bugs if not handled carefully.

If the last row in the frame has NULL, LAST_VALUE returns NULL. Adjusting frame boundaries or filtering NULLs beforehand is necessary. Also, some databases differ in default frame behavior, so testing is important.

Result

Correct handling of NULLs and frames ensures LAST_VALUE returns meaningful results.

Knowing these subtleties prevents silent data errors in production queries.

Under the Hood

FIRST_VALUE and LAST_VALUE operate as window functions that scan a defined partition of rows ordered by specified columns. FIRST_VALUE always picks the value from the first row in the window frame. LAST_VALUE picks from the last row in the current frame, which by default ends at the current row, so its result depends on the frame definition. The database engine processes these functions during query execution by maintaining the window frame and applying the function to each row without collapsing the result set.

Why designed this way?

These functions were designed to provide easy access to boundary values within groups without requiring complex joins or subqueries. The default frame for LAST_VALUE ending at the current row allows flexible cumulative calculations but requires explicit frame extension to get the true last value. This design balances flexibility and performance, allowing various analytical queries with a single syntax.

Query Execution Flow:

┌───────────────┐
│ Input Table   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Partition By  │
│ (group rows)  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Order By      │
│ (sort rows)   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Window Frame  │
│ (define range)│
└──────┬────────┘
       │
       ▼
┌─────────────────────────────┐
│ FIRST_VALUE / LAST_VALUE     │
│ (pick first or last value)   │
└──────────────┬──────────────┘
               │
               ▼
       ┌───────────────┐
       │ Output Rows   │
       │ (with values) │
       └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does LAST_VALUE always return the last row in the entire group? Commit yes or no.

Common Belief: LAST_VALUE always returns the last value in the entire partition regardless of frame.

Reality: LAST_VALUE returns the last value in the current window frame, which by default ends at the current row, so it may return the current row's value instead of the true last.

Why it matters: This causes unexpected results in queries if the frame is not explicitly set, leading to incorrect data analysis.

Quick: Does FIRST_VALUE remove other rows and return only one row? Commit yes or no.

Common Belief: FIRST_VALUE returns only one row with the first value, removing others.

Reality: FIRST_VALUE returns the first value for each row in the partition without removing any rows; it adds a column with that value.

Why it matters: Misunderstanding this leads to confusion about query results and incorrect expectations about data shape.

Quick: Does FIRST_VALUE ignore NULL values when picking the first? Commit yes or no.

Common Belief: FIRST_VALUE skips NULLs and returns the first non-NULL value.

Reality: FIRST_VALUE returns the value in the first row according to order, even if it is NULL.

Why it matters: This can cause NULLs to appear unexpectedly in results if not handled, affecting data quality.

Quick: Can you use FIRST_VALUE and LAST_VALUE without ORDER BY? Commit yes or no.

Common Belief: You can use FIRST_VALUE and LAST_VALUE without specifying ORDER BY and still get meaningful results.

Reality: ORDER BY is required to define the order of rows; without it, the functions' results are undefined or meaningless.

Why it matters: Omitting ORDER BY leads to unpredictable results and bugs in data analysis.

Expert Zone

1

LAST_VALUE's default frame ends at the current row, so to get the true last value, you must specify a frame that includes all rows, like ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

2

Some SQL databases differ in how they implement window frame defaults, so cross-platform queries using LAST_VALUE may behave differently and require testing.

3

Using FIRST_VALUE or LAST_VALUE with large partitions and complex ordering can impact performance; indexing and query plans should be reviewed for optimization.

When NOT to use

Avoid FIRST_VALUE and LAST_VALUE when you need aggregated results that collapse rows, such as total sums or counts. Use aggregate functions like MIN, MAX, or subqueries when you want a single summary row per group instead of repeating values on every row.

Production Patterns

In production, FIRST_VALUE is often used to show the earliest event date or highest priority item per group alongside detailed rows. LAST_VALUE is used with explicit frame clauses to get the latest status or value. Both are combined with filters and joins to build dashboards, reports, and time-series analyses efficiently.

Connections

Window Functions

FIRST_VALUE and LAST_VALUE are specific types of window functions.

Understanding window functions as a whole helps grasp how FIRST_VALUE and LAST_VALUE operate over partitions and frames.

Aggregate Functions

FIRST_VALUE and LAST_VALUE provide boundary values without collapsing rows, unlike aggregate functions which summarize data.

Knowing the difference clarifies when to use window functions versus aggregates for data analysis.

Time Series Analysis

FIRST_VALUE and LAST_VALUE help identify start and end points in ordered time series data.

Recognizing this connection helps apply these functions to real-world problems like tracking events over time.

Common Pitfalls

#1 LAST_VALUE returns unexpected current row value instead of last in group.

Wrong approach:

SELECT department, employee, salary,
LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary) AS last_salary
FROM employees;

Correct approach:

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

Root cause:

Not specifying the window frame causes LAST_VALUE to use the default frame ending at the current row.

#2 Using FIRST_VALUE without ORDER BY leads to unpredictable results.

Wrong approach:

SELECT department, employee, salary,
FIRST_VALUE(salary) OVER (PARTITION BY department) AS first_salary
FROM employees;

Correct approach:

SELECT department, employee, salary,
FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary) AS first_salary
FROM employees;

Root cause:

ORDER BY is required to define the order for FIRST_VALUE to pick the correct row.

#3 Expecting FIRST_VALUE to skip NULLs automatically.

Wrong approach:

SELECT department, employee, salary,
FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary) AS first_salary
FROM employees;
-- Some first_salary values are NULL

Correct approach:

SELECT department, employee, salary,
FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY CASE WHEN salary IS NULL THEN 1 ELSE 0 END, salary) AS first_non_null_salary
FROM employees;

Root cause:

FIRST_VALUE returns the first row's value as ordered, including NULLs, unless ordering is adjusted.

Key Takeaways

FIRST_VALUE and LAST_VALUE are window functions that return the first or last value in an ordered group without removing rows.

ORDER BY inside the window clause is essential to define which row is first or last.

LAST_VALUE depends on the window frame and often requires explicit frame clauses to return the true last value.

These functions simplify queries that need boundary values per group, improving readability and performance.

Understanding their behavior with NULLs and frames prevents common bugs in data analysis.