GREATEST and LEAST in MySQL

Introduction

GREATEST and LEAST help you find the biggest or smallest value from a list of values easily.

When you want to compare multiple numbers and pick the highest one.

When you want to find the smallest value among several columns in a table.

When you need to decide between different options based on their values.

When you want to simplify your query by avoiding multiple IF or CASE statements.

Syntax

MySQL

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

You can use any number of values inside the parentheses.

Values can be columns, constants, or expressions.

Examples

Returns the largest number among 10, 20, and 30.

MySQL

SELECT GREATEST(10, 20, 30);

Returns the smallest number among 10, 20, and 30.

MySQL

SELECT LEAST(10, 20, 30);

Finds the highest value among price, discount, and 100 for each product.

MySQL

SELECT GREATEST(price, discount, 100) FROM products;

Finds the lowest score among three columns for each exam record.

MySQL

SELECT LEAST(score1, score2, score3) FROM exams;

Sample Program

This creates a table with students' scores in three subjects. Then it shows each student's highest and lowest score using GREATEST and LEAST.

MySQL

CREATE TABLE test_scores (
  student VARCHAR(20),
  math INT,
  science INT,
  english INT
);

INSERT INTO test_scores VALUES
('Alice', 85, 90, 78),
('Bob', 70, 65, 80),
('Charlie', 95, 88, 92);

SELECT student, 
       GREATEST(math, science, english) AS highest_score, 
       LEAST(math, science, english) AS lowest_score
FROM test_scores;

Important Notes

If any value is NULL, the result of GREATEST or LEAST will be NULL.

All values are compared as the same type, so mixing strings and numbers may give unexpected results.

Summary

GREATEST returns the largest value from a list.

LEAST returns the smallest value from a list.

They simplify comparing multiple values in SQL queries.