Return the Largest or Smallest of Several Values

Use `GREATEST` and `LEAST` to compare multiple values in a single expression without a `CASE WHEN` chain.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Find the effective (lower) price and the higher reference price for each product

LEAST(list_price, sale_price) picks whichever column holds the smaller number per row. For Doohickey, the sale price (90) is actually higher than the list price (75), so the list price becomes the effective price. Gadget's two prices are equal so both functions return 50. Widget has a sale_price of 80, which is lower than the list_price of 100.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE products (name VARCHAR(50), list_price INT, sale_price INT);

INSERT INTO
  products
VALUES
  ('Widget', 100, 80),
  ('Gadget', 50, 50),
  ('Doohickey', 75, 90);

Validated query Shared across supported engines.

SQL

SELECT
  name,
  LEAST (list_price, sale_price) AS effective_price,
  GREATEST (list_price, sale_price) AS higher_price
FROM
  products
ORDER BY
  name;

Expected result Returned rows for the shared example.

name	effective_price	higher_price
Doohickey	75	90
Gadget	50	50
Widget	80	100

The GREATEST and LEAST syntax is identical across all supported engines.

Useful when

Where this command helps.

capping a value so it never falls below a floor or exceeds a ceiling
picking the earlier or later of two date columns per row without a subquery

Explanation

What the command is doing.

GREATEST(a, b, ...) returns the largest of its arguments; LEAST(a, b, ...) returns the smallest. Both functions accept any number of arguments, skip NULL values if at least one argument is non-null, and work with numbers, strings, and dates. They are supported natively in MySQL, MariaDB, PostgreSQL, and SQLite (3.38+). SQL Server added them in version 2022. The common alternative for older SQL Server is a CASE WHEN a > b THEN a ELSE b END expression.

Return the Largest or Smallest of Several Values

Find the effective (lower) price and the higher reference price for each product

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.