How to Use first_value Function in PostgreSQL: Syntax and Examples

In PostgreSQL, use the first_value() window function to get the first value in an ordered set of rows within a partition. It requires an OVER() clause with optional PARTITION BY and ORDER BY to define the window frame.

Syntax

The first_value() function syntax is:

• first_value(expression) OVER ( [PARTITION BY partition_expression] ORDER BY order_expression [ROWS frame_specification] )

Explanation:

• expression: The column or value to return the first of.
• PARTITION BY: Divides rows into groups to apply the function separately.
• ORDER BY: Defines the order of rows to determine the first value.
• ROWS frame_specification: Optional, defines the window frame rows.

sql

first_value(expression) OVER (PARTITION BY partition_expression ORDER BY order_expression [ROWS frame_specification])

Example

This example shows how to get the first sale amount per salesperson ordered by sale date.

sql

CREATE TABLE sales (
  salesperson TEXT,
  sale_date DATE,
  amount INT
);

INSERT INTO sales VALUES
('Alice', '2024-01-01', 100),
('Alice', '2024-01-05', 150),
('Bob', '2024-01-02', 200),
('Bob', '2024-01-03', 50);

SELECT
  salesperson,
  sale_date,
  amount,
  first_value(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) AS first_sale_amount
FROM sales
ORDER BY salesperson, sale_date;

Output

 salesperson | sale_date  | amount | first_sale_amount 
-------------+------------+--------+-------------------
 Alice       | 2024-01-01 |    100 |               100
 Alice       | 2024-01-05 |    150 |               100
 Bob         | 2024-01-02 |    200 |               200
 Bob         | 2024-01-03 |     50 |               200

Common Pitfalls

Common mistakes when using first_value() include:

• Not specifying ORDER BY inside OVER(), which leads to unpredictable results.
• Confusing first_value() with aggregate functions like MIN() which return the minimum value, not the first in order.
• Using first_value() without PARTITION BY when you want grouped results, causing the function to consider the entire dataset.

sql

/* Wrong: missing ORDER BY, result unpredictable */
SELECT salesperson, amount, first_value(amount) OVER () AS first_amount FROM sales;

/* Right: specify ORDER BY to get correct first value */
SELECT salesperson, amount, first_value(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) AS first_amount FROM sales;

Quick Reference

Clause	Purpose
expression	Column or value to return the first of
PARTITION BY	Groups rows to apply function separately
ORDER BY	Defines row order to pick the first value
ROWS frame_specification	Optional window frame definition

Key Takeaways

Use first_value() with OVER() including ORDER BY to get the first value in a window.

PARTITION BY divides data into groups for separate first values.

Without ORDER BY, first_value() returns unpredictable results.

first_value() returns the first row's value, not the minimum or maximum.

Always test your window function queries to confirm expected output.