FIRST_VALUE and LAST_VALUE in SQL

Introduction

These functions help you find the first or last value in a group of rows, making it easy to pick important data points without sorting manually.

When you want to find the earliest date or first item in a list for each group.

When you need the last recorded value in a series of events.

When summarizing data by groups and want to show starting or ending values.

When comparing the first and last values in a time series or ordered data.

When creating reports that highlight beginning or ending states.

Syntax

SQL

FIRST_VALUE(column_name) OVER (PARTITION BY group_column ORDER BY order_column [ROWS BETWEEN ...])

LAST_VALUE(column_name) OVER (PARTITION BY group_column ORDER BY order_column [ROWS BETWEEN ...])

Use OVER() clause to define how to group and order rows.

Ordering is important to determine what is 'first' or 'last'.

Examples

Finds the highest salary in each department by ordering salaries descending and picking the first.

SQL

SELECT
  employee_id,
  department_id,
  FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS highest_salary
FROM employees;

Finds the last order date for each customer by looking at all their orders.

SQL

SELECT
  order_id,
  customer_id,
  LAST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order_date
FROM orders;

Sample Program

This query shows the first and last sale amounts for each region based on the sale date.

SQL

CREATE TABLE sales (
  id INT,
  region VARCHAR(10),
  sale_date DATE,
  amount INT
);

INSERT INTO sales VALUES
(1, 'East', '2024-01-01', 100),
(2, 'East', '2024-01-05', 150),
(3, 'West', '2024-01-03', 200),
(4, 'West', '2024-01-10', 250);

SELECT
  region,
  sale_date,
  amount,
  FIRST_VALUE(amount) OVER (PARTITION BY region ORDER BY sale_date) AS first_sale_amount,
  LAST_VALUE(amount) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_amount
FROM sales
ORDER BY region, sale_date;

Important Notes

LAST_VALUE needs a frame clause like ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to work as expected; otherwise, it may return the current row's value.

These functions do not change the number of rows; they add columns with the first or last values.

Ordering inside OVER() is crucial to define what 'first' and 'last' mean.

Summary

FIRST_VALUE returns the first value in an ordered group of rows.

LAST_VALUE returns the last value but often needs a frame to look at all rows.

Both use the OVER() clause to group and order data.