GREATEST and LEAST functions in PostgreSQL

Introduction

These functions help you find the biggest or smallest value from a list of values quickly, like choosing the highest or lowest score.

When you want to find the highest price among several products.

When you need to get the earliest or latest date from multiple dates.

When comparing scores from different tests to find the best or worst.

When deciding the minimum or maximum value from several columns in a table.

Syntax

PostgreSQL

GREATEST(value1, value2, ..., valueN)
LEAST(value1, value2, ..., valueN)

Both functions take two or more values as input.

They return the largest (GREATEST) or smallest (LEAST) value among the inputs.

Examples

Returns 20 because it is the largest number.

PostgreSQL

SELECT GREATEST(10, 20, 15);

Returns 10 because it is the smallest number.

PostgreSQL

SELECT LEAST(10, 20, 15);

Returns 'cherry' because it is last alphabetically.

PostgreSQL

SELECT GREATEST('apple', 'banana', 'cherry');

Returns 'apple' because it is first alphabetically.

PostgreSQL

SELECT LEAST('apple', 'banana', 'cherry');

Sample Program

This creates a table with students and their test scores. Then it finds each student's highest and lowest test score using GREATEST and LEAST.

PostgreSQL

CREATE TABLE scores (
  student VARCHAR(20),
  test1 INT,
  test2 INT,
  test3 INT
);

INSERT INTO scores VALUES
('Alice', 85, 90, 78),
('Bob', 70, 88, 95),
('Carol', 92, 85, 87);

SELECT student, GREATEST(test1, test2, test3) AS highest_score, LEAST(test1, test2, test3) AS lowest_score
FROM scores;

Important Notes

If any input value is NULL, the result is NULL unless you handle NULLs explicitly.

GREATEST and LEAST work with numbers, strings, and dates, comparing them according to their type.

They are very useful for quick comparisons without writing complex CASE statements.

Summary

GREATEST returns the biggest value from a list.

LEAST returns the smallest value from a list.

Use them to compare multiple values easily in queries.