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.

Practice

1. What does the FIRST_VALUE function do in PostgreSQL?

easy

A. Returns the first value in a sorted group of rows.

B. Returns the last value in a sorted group of rows.

C. Returns the average value of a column.

D. Returns the total count of rows.

Solution

1. Step 1: Understand the purpose of FIRST_VALUE

   FIRST_VALUE returns the first value in a sorted window or group of rows.

2. Step 2: Compare with other options

   Options B, C, and D describe different functions or aggregates, not FIRST_VALUE.

3. Final Answer:

   Returns the first value in a sorted group of rows. -> Option A

4. Quick Check:

   FIRST_VALUE = first value in sorted rows [OK]

Hint: FIRST_VALUE always picks the first row's value in order [OK]

Common Mistakes:

• Confusing FIRST_VALUE with LAST_VALUE
• Thinking it returns an aggregate like sum or count
• Ignoring the sorting order in the window

2. Which of the following is the correct syntax to get the last value of a column score ordered by date using LAST_VALUE in PostgreSQL?

easy

A. SELECT LAST_VALUE(score) FROM results ORDER BY date;

B. SELECT LAST_VALUE(score) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM results;

C. SELECT LAST_VALUE(score) OVER (PARTITION BY date) FROM results;

D. SELECT LAST_VALUE(score) OVER (ORDER BY date) FROM results;

Solution

1. Step 1: Understand LAST_VALUE default frame

   LAST_VALUE by default looks from current row to end, so it may not return the last value overall without framing.

2. Step 2: Use explicit window frame to get true last value

   Adding ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ensures the entire partition is considered.

3. Final Answer:

   SELECT LAST_VALUE(score) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM results; -> Option B

4. Quick Check:

   LAST_VALUE needs full frame to get last value [OK]

Hint: Add full window frame to LAST_VALUE for correct last row [OK]

Common Mistakes:

• Omitting window frame causing wrong last value
• Using PARTITION BY incorrectly
• Using LAST_VALUE without OVER() clause

3. Given the table sales with columns region, month, and revenue, what will this query return?

SELECT region, month, revenue, FIRST_VALUE(revenue) OVER (PARTITION BY region ORDER BY month) AS first_rev FROM sales ORDER BY region, month;

medium

A. The total revenue summed for each region.

B. The revenue of the last month for each region repeated on every row of that region.

C. The revenue of the first month for each region repeated on every row of that region.

D. The revenue of the current month only.

Solution

1. Step 1: Analyze FIRST_VALUE with PARTITION and ORDER BY

   FIRST_VALUE(revenue) OVER (PARTITION BY region ORDER BY month) returns the first revenue value per region ordered by month.

2. Step 2: Understand output per row

   For each row in a region, the first month's revenue is repeated as first_rev.

3. Final Answer:

   The revenue of the first month for each region repeated on every row of that region. -> Option C

4. Quick Check:

   FIRST_VALUE repeats first row's value per partition [OK]

Hint: FIRST_VALUE repeats first sorted value per group [OK]

Common Mistakes:

• Thinking it returns last value instead
• Assuming it sums or aggregates
• Ignoring PARTITION BY effect

4. You wrote this query but it returns unexpected results:

SELECT employee_id, salary, LAST_VALUE(salary) OVER (ORDER BY employee_id) AS last_sal FROM employees;

What is the likely cause?

medium

A. LAST_VALUE cannot be used without PARTITION BY clause.

B. ORDER BY employee_id is invalid in window functions.

C. salary column must be aggregated with SUM before LAST_VALUE.

D. LAST_VALUE needs a window frame to include all rows; default frame causes wrong output.

Solution

1. Step 1: Recall LAST_VALUE default frame behavior

   By default, LAST_VALUE looks from current row to end, so it may not return the last row's value overall.

2. Step 2: Identify missing window frame

   Without specifying ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, the last value is not the last row's value.

3. Final Answer:

   LAST_VALUE needs a window frame to include all rows; default frame causes wrong output. -> Option D

4. Quick Check:

   LAST_VALUE requires full frame for correct last row [OK]

Hint: Add full frame to LAST_VALUE to fix wrong results [OK]

Common Mistakes:

• Assuming ORDER BY alone fixes LAST_VALUE
• Thinking PARTITION BY is mandatory
• Trying to aggregate salary before LAST_VALUE

5. You want to find the first and last sale amount for each customer ordered by sale date. Which query correctly returns both values per customer in PostgreSQL?

hard

A. SELECT customer_id, sale_date, sale_amount, FIRST_VALUE(sale_amount) OVER (PARTITION BY customer_id ORDER BY sale_date), LAST_VALUE(sale_amount) OVER (PARTITION BY customer_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM sales;

B. SELECT customer_id, sale_date, sale_amount, FIRST_VALUE(sale_amount), LAST_VALUE(sale_amount) FROM sales GROUP BY customer_id;

C. SELECT customer_id, sale_date, sale_amount, FIRST_VALUE(sale_amount) OVER (ORDER BY sale_date), LAST_VALUE(sale_amount) OVER (ORDER BY sale_date) FROM sales;

D. SELECT customer_id, sale_date, sale_amount, MIN(sale_amount) OVER (PARTITION BY customer_id), MAX(sale_amount) OVER (PARTITION BY customer_id) FROM sales;

Solution

1. Step 1: Use FIRST_VALUE with PARTITION and ORDER BY

   FIRST_VALUE(sale_amount) OVER (PARTITION BY customer_id ORDER BY sale_date) gets first sale per customer.

2. Step 2: Use LAST_VALUE with full window frame

   LAST_VALUE needs ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get last sale per customer.

3. Step 3: Check other options

   SELECT customer_id, sale_date, sale_amount, FIRST_VALUE(sale_amount), LAST_VALUE(sale_amount) FROM sales GROUP BY customer_id; lacks OVER() and ORDER BY, C lacks PARTITION BY, D uses MIN/MAX which is different.

4. Final Answer:

   SELECT customer_id, sale_date, sale_amount, FIRST_VALUE(sale_amount) OVER (PARTITION BY customer_id ORDER BY sale_date), LAST_VALUE(sale_amount) OVER (PARTITION BY customer_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM sales; -> Option A

5. Quick Check:

   Combine FIRST_VALUE and LAST_VALUE with proper framing [OK]

Hint: Use full frame for LAST_VALUE with PARTITION and ORDER BY [OK]

Common Mistakes:

• Omitting window frame for LAST_VALUE
• Using aggregate functions instead of window functions
• Not partitioning by customer_id