FIRST_VALUE and LAST_VALUE in PostgreSQL

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 extra work.

When you want to find the earliest or latest date in a list of events.

When you need the first or last price of a product in a sales report.

When you want to show the first or last comment made by a user in a chat.

When you want to compare the first and last scores of a player in a game.

When you want to find the first or last entry in a sorted list of records.

Syntax

PostgreSQL

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

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

These functions are used with the OVER() clause to define how to group and order rows.

You can use PARTITION BY to group rows and ORDER BY to sort them before picking the first or last value.

Examples

Finds the lowest salary in the employees table.

PostgreSQL

SELECT FIRST_VALUE(salary) OVER (ORDER BY salary) AS lowest_salary FROM employees;

Finds the first hired employee in each department.

PostgreSQL

SELECT department, FIRST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date) AS first_hired FROM employees;

Finds the last hired employee in each department. The ROWS clause ensures the window covers all rows.

PostgreSQL

SELECT department, LAST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hired FROM employees;

Sample Program

This query shows each employee with their department and hire date, plus the first and last hired employee in their department.

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  employee_name TEXT,
  department TEXT,
  hire_date DATE,
  salary INT
);

INSERT INTO employees (employee_name, department, hire_date, salary) VALUES
('Alice', 'Sales', '2020-01-15', 50000),
('Bob', 'Sales', '2021-03-10', 52000),
('Charlie', 'HR', '2019-07-01', 48000),
('Diana', 'HR', '2022-05-20', 47000),
('Eve', 'IT', '2021-11-11', 60000);

SELECT department, employee_name, hire_date,
  FIRST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date) AS first_hired,
  LAST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hired
FROM employees
ORDER BY department, hire_date;

Important Notes

Without the ROWS BETWEEN clause, LAST_VALUE may return unexpected results because it looks at the current row and following rows by default.

Use ORDER BY inside OVER() to define which row is considered first or last.

FIRST_VALUE and LAST_VALUE work well with PARTITION BY to handle groups separately.

Summary

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

LAST_VALUE returns the last value in a sorted group of rows, but needs careful window framing.

Both use the OVER() clause to define grouping and order.