FIRST_VALUE and LAST_VALUE in PostgreSQL - Deep Dive

Overview - FIRST_VALUE and LAST_VALUE

What is it?

FIRST_VALUE and LAST_VALUE are SQL window functions that return the first and last value in a set of rows within a defined window. They help you look at values from other rows related to the current row without grouping or collapsing the data. These functions are useful for comparing or referencing boundary values in ordered data.

Why it matters

Without FIRST_VALUE and LAST_VALUE, you would need complex queries or multiple joins to find the first or last item in a group, which is slow and hard to read. These functions simplify queries and improve performance when analyzing ordered data, like finding the earliest or latest event per category. They make data analysis more intuitive and efficient.

Where it fits

Before learning these, you should understand basic SQL SELECT queries, ORDER BY, and the concept of window functions like ROW_NUMBER or RANK. After mastering FIRST_VALUE and LAST_VALUE, you can explore more advanced window functions and frame clauses to perform complex analytics.

Mental Model

Core Idea

FIRST_VALUE and LAST_VALUE pick the first or last value from a group of rows ordered in a specific way, without collapsing the rows.

Think of it like...

Imagine a line of people sorted by height. FIRST_VALUE is like asking who is the shortest person in the line, and LAST_VALUE is like asking who is the tallest, but you still see everyone in the line.

Window Function Frame:
┌─────────────┐
│ Row 1       │
│ Row 2       │
│ Row 3       │ ← Current Row
│ Row 4       │
│ Row 5       │
└─────────────┘

FIRST_VALUE returns value from Row 1
LAST_VALUE returns value from Row 5

All rows remain visible; values come from frame edges.

Build-Up - 7 Steps

1

Foundation

Understanding Window Functions Basics

Concept: Window functions operate on a set of rows related to the current row without collapsing them.

In SQL, window functions let you perform calculations across rows related to the current row. Unlike GROUP BY, they keep all rows visible. For example, ROW_NUMBER() assigns a number to each row in order.

Result

You get a new column showing row numbers without losing any rows.

Understanding that window functions keep all rows visible is key to grasping how FIRST_VALUE and LAST_VALUE work.

2

Foundation

Ordering Rows Within a Window

Concept: Window functions often require ordering rows to define the frame for calculations.

You specify ORDER BY inside the OVER() clause to tell SQL how to sort rows before applying the function. For example, ORDER BY date ASC orders rows from earliest to latest.

Result

Rows are sorted logically for the window function to process in order.

Knowing how to order rows is essential because FIRST_VALUE and LAST_VALUE depend on this order to pick values.

3

Intermediate

Using FIRST_VALUE to Get Earliest Value

🤔Before reading on: do you think FIRST_VALUE always returns the very first row in the entire table or just within a group? Commit to your answer.

Concept: FIRST_VALUE returns the first value in the ordered window frame for each row.

You write FIRST_VALUE(column) OVER (PARTITION BY group ORDER BY column) to get the earliest value per group. The function looks at the ordered rows in the window and picks the first one for each row's frame.

Result

Each row shows the earliest value in its group, repeated for all rows in that group.

Understanding that FIRST_VALUE repeats the first value for every row in the window helps you use it for comparisons or reference.

4

Intermediate

Using LAST_VALUE and Frame Boundaries

🤔Before reading on: do you think LAST_VALUE returns the last value in the entire partition by default or only up to the current row? Commit to your answer.

Concept: LAST_VALUE returns the last value in the current window frame, which by default ends at the current row.

By default, LAST_VALUE looks from the start of the partition to the current row, so it returns the current row's value, not the last in the partition. To get the true last value, you must adjust the frame with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Result

Without frame adjustment, LAST_VALUE returns the current row's value; with adjustment, it returns the last value in the partition.

Knowing the default frame behavior prevents mistakes where LAST_VALUE seems to behave like the current row's value.

5

Intermediate

Adjusting Window Frames for Accurate LAST_VALUE

Concept: You can change the window frame to include all rows in the partition to get the true last value.

Use LAST_VALUE(column) OVER (PARTITION BY group ORDER BY column ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) to include all rows in the frame. This makes LAST_VALUE return the last value in the entire group, not just up to the current row.

Result

Each row shows the last value in its group, repeated for all rows.

Understanding how to control window frames is crucial for correct LAST_VALUE results.

6

Advanced

Combining FIRST_VALUE and LAST_VALUE for Range Analysis

🤔Before reading on: do you think you can use FIRST_VALUE and LAST_VALUE together to find the range of values per group? Commit to your answer.

Concept: You can use both functions in the same query to find the start and end values of ordered groups.

For example, in sales data, FIRST_VALUE(sale_date) and LAST_VALUE(sale_date) with proper frames show the earliest and latest sale dates per customer. This helps analyze time ranges without grouping.

Result

Each row shows the full range of dates for its group alongside individual row data.

Knowing how to combine these functions lets you analyze boundaries and trends within groups efficiently.

7

Expert

Performance and Frame Optimization in PostgreSQL

🤔Before reading on: do you think using large window frames with LAST_VALUE impacts query performance? Commit to your answer.

Concept: Large window frames can slow queries because PostgreSQL must scan many rows per calculation; optimizing frames improves speed.

Using ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING forces PostgreSQL to consider all rows in the partition for each row, which can be costly. Sometimes RANGE frames or smaller frames improve performance. Also, indexing the ORDER BY columns helps.

Result

Optimized queries run faster and use fewer resources.

Understanding the cost of frame definitions helps write efficient queries in production.

Under the Hood

PostgreSQL processes window functions by first sorting rows according to the ORDER BY clause within each partition. Then, for each row, it applies the function over the defined frame. FIRST_VALUE simply picks the first row's value in the frame, while LAST_VALUE picks the last row's value. The frame defaults to rows from the start of the partition to the current row, which affects LAST_VALUE's output unless explicitly changed.

Why designed this way?

Window functions were designed to allow calculations across related rows without collapsing data, preserving row-level detail. The default frame ending at the current row supports cumulative calculations, which are common. Allowing frame customization gives flexibility for different analytic needs.

Partitioned Data:
┌───────────────┐
│ Partition 1   │
│ ┌───────────┐ │
│ │ Row 1     │ │
│ │ Row 2     │ │
│ │ Row 3     │ │
│ └───────────┘ │
│ Partition 2   │
│ ┌───────────┐ │
│ │ Row 4     │ │
│ │ Row 5     │ │
│ └───────────┘ │
└───────────────┘

For each row:
  1. Sort rows in partition by ORDER BY
  2. Define frame (default: start to current row)
  3. FIRST_VALUE picks first row in frame
  4. LAST_VALUE picks last row in frame

Frame controls output values.

Myth Busters - 4 Common Misconceptions

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

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

Reality: By default, LAST_VALUE returns the last value in the frame ending at the current row, often the current row itself.

Why it matters: Misunderstanding this leads to wrong query results, especially when expecting the true last value in a group.

Quick: Does FIRST_VALUE change as you move down rows in the partition? Commit yes or no.

Common Belief: FIRST_VALUE changes for each row depending on the current row position.

Reality: FIRST_VALUE always returns the first value in the ordered frame, which is usually the same for all rows in the partition.

Why it matters: Expecting it to change causes confusion and incorrect query logic.

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

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

Reality: ORDER BY is required to define the order; without it, FIRST_VALUE's result is unpredictable.

Why it matters: Omitting ORDER BY leads to inconsistent and unreliable outputs.

Quick: Does changing the frame clause affect FIRST_VALUE results? Commit yes or no.

Common Belief: Changing the frame clause does not affect FIRST_VALUE results.

Reality: Changing the frame can affect FIRST_VALUE if the frame excludes the first row.

Why it matters: Ignoring frame effects can cause subtle bugs in analytics.

Expert Zone

1

LAST_VALUE's default frame ending at the current row is designed for cumulative calculations, but often surprises users expecting the last partition value.

2

Using RANGE frames instead of ROWS can cause unexpected behavior with FIRST_VALUE and LAST_VALUE when there are duplicate ORDER BY values.

3

Indexing the columns used in PARTITION BY and ORDER BY can significantly improve performance of window functions on large datasets.

When NOT to use

Avoid FIRST_VALUE and LAST_VALUE when you need aggregated single-row results; use GROUP BY with MIN or MAX instead. Also, if your dataset is huge and performance is critical, consider pre-aggregating or using materialized views to reduce window function overhead.

Production Patterns

In real systems, FIRST_VALUE and LAST_VALUE are used for sessionization (finding session start/end), trend analysis (first/last event per user), and reporting (earliest/latest dates). They often appear with frame clauses to fine-tune results and combined with other window functions for complex analytics.

Connections

Aggregate Functions (MIN, MAX)

FIRST_VALUE and LAST_VALUE provide similar boundary values but keep row-level detail unlike aggregates.

Understanding the difference helps choose between collapsing data with aggregates or preserving detail with window functions.

Cumulative Sums (Running Totals)

Both use window frames and ordering to calculate values across rows, often combined in analytics.

Knowing how frames affect cumulative sums clarifies why LAST_VALUE defaults to the current row frame.

Project Management Timelines

Finding first and last milestones in a timeline is conceptually similar to FIRST_VALUE and LAST_VALUE in ordered data.

Recognizing this connection helps understand how these functions identify boundaries in sequences.

Common Pitfalls

#1 LAST_VALUE returns current row's value, not the last in partition.

Wrong approach:

SELECT LAST_VALUE(sale_date) OVER (PARTITION BY customer_id ORDER BY sale_date) FROM sales;

Correct approach:

SELECT LAST_VALUE(sale_date) OVER (PARTITION BY customer_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM sales;

Root cause:

Not adjusting the window frame causes LAST_VALUE to consider only rows up to the current one.

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

Wrong approach:

SELECT FIRST_VALUE(sale_date) OVER (PARTITION BY customer_id) FROM sales;

Correct approach:

SELECT FIRST_VALUE(sale_date) OVER (PARTITION BY customer_id ORDER BY sale_date) FROM sales;

Root cause:

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

#3 Expecting FIRST_VALUE to change per row in the partition.

Wrong approach:

Using FIRST_VALUE to get a different value for each row without changing the frame or order.

Correct approach:

Use ROW_NUMBER() or other functions if you want row-specific values; FIRST_VALUE returns the first in the frame.

Root cause:

Misunderstanding that FIRST_VALUE always returns the first value in the ordered frame.

Key Takeaways

FIRST_VALUE and LAST_VALUE are window functions that return the first and last values in an ordered set of rows without collapsing the data.

The ORDER BY clause inside the window function defines the order, which is essential for correct results.

LAST_VALUE's default frame ends at the current row, so you must adjust the frame to get the true last value in a partition.

These functions help analyze boundaries and trends within groups while preserving all rows for detailed insights.

Understanding window frames and their impact on these functions is crucial for writing accurate and efficient queries.