GREATEST and LEAST functions in PostgreSQL - Step-by-Step Execution

Concept Flow - GREATEST and LEAST functions

Input values

↓

Compare values pairwise

↓

Find Greatest value

→Return Greatest

↓

Find Least value

→Return Least

GREATEST and LEAST take multiple values, compare them, and return the largest or smallest value respectively.

Execution Sample

PostgreSQL

SELECT GREATEST(3, 7, 2) AS max_val, LEAST(3, 7, 2) AS min_val;

This query returns the largest and smallest values among 3, 7, and 2.

Execution Table

Step	Values Compared	GREATEST Result	LEAST Result	Explanation
1	3, 7	7	3	Compare first two values: 7 is greater, 3 is smaller
2	7, 2	7	2	Compare previous greatest and next value: 7 still greatest, 2 smaller than previous least 3
3	Final	7	2	Return final greatest and least values
4	End	7	2	No more values to compare, query ends

💡 All values compared, final greatest is 7 and least is 2

Variable Tracker

Variable	Start	After 1	After 2	Final
GREATEST	N/A	7	7	7
LEAST	N/A	3	2	2

Key Moments - 2 Insights

Why does GREATEST return 7 and not 3 or 2?

Because in the execution_table rows 1 and 2, 7 is compared with other values and found to be the largest.

Why does LEAST return 2 instead of 3?

Row 2 shows LEAST compares 3 and 2, and 2 is smaller, so it updates the least value to 2.

Visual Quiz - 3 Questions

Look at the execution_table, what is the GREATEST result after step 1?

A 7

B 3

C 2

D N/A

💡 Hint

Check the 'GREATEST Result' column in row 1 of execution_table

At which step does LEAST update from 3 to 2?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look at the 'LEAST Result' column in execution_table rows 1 and 2

If we add value 10 to the inputs, what would be the final GREATEST?

A 7

B 10

C 3

D 2

💡 Hint

GREATEST returns the largest value among all inputs

Concept Snapshot

GREATEST(value1, value2, ...) returns the largest value.
LEAST(value1, value2, ...) returns the smallest value.
They compare all inputs and return one value.
Useful for quick max/min among columns or expressions.
NULL inputs cause NULL result unless handled.

Full Transcript

The GREATEST and LEAST functions in PostgreSQL take multiple input values and compare them to find the largest and smallest values respectively. The process starts by comparing the first two values, updating the current greatest or least value. Then it compares the current greatest or least with the next value, updating if needed. This continues until all values are compared. The final greatest and least values are returned. For example, with inputs 3, 7, and 2, GREATEST returns 7 and LEAST returns 2. If a NULL is present, the result is NULL unless handled. This visual trace shows each comparison step and how the results update.