GREATEST and LEAST functions in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the GREATEST function do in SQL?

The GREATEST function returns the largest value from a list of expressions. It compares all values and gives back the highest one.

beginner

What does the LEAST function do in SQL?

The LEAST function returns the smallest value from a list of expressions. It compares all values and gives back the lowest one.

intermediate

How does GREATEST handle NULL values in PostgreSQL?

If any argument is NULL, GREATEST returns NULL unless you use COALESCE to replace NULLs with a value.

beginner

Write a simple example using GREATEST to find the highest score among three columns: score1, score2, score3.

SELECT GREATEST(score1, score2, score3) AS highest_score FROM your_table;

intermediate

Can GREATEST and LEAST be used with different data types like numbers and strings?

Yes, but all arguments must be comparable and usually of the same or compatible data types. For example, comparing strings works alphabetically.

What will GREATEST(5, 10, 3) return?

A NULL

B 5

C 3

D 10

GREATEST returns the largest value, which is 10.

What does LEAST('apple', 'banana', 'cherry') return?

A 'banana'

B 'cherry'

C 'apple'

D NULL

LEAST returns the smallest value alphabetically, which is 'apple'.

If one argument is NULL, what does GREATEST(4, NULL, 7) return?

A NULL

B 4

C Error

D 7

GREATEST returns NULL if any argument is NULL unless handled with COALESCE.

Which function would you use to find the smallest value among columns?

A GREATEST

B LEAST

C MAX

D MIN

LEAST returns the smallest value among the given expressions.

Can GREATEST be used to compare dates in PostgreSQL?

A Yes, it returns the latest date.

B No, it only works with numbers.

C Yes, but only with strings.

D No, it causes an error.

GREATEST can compare dates and returns the latest (greatest) date.

Explain how the GREATEST and LEAST functions work and give a simple example for each.

Think about comparing values to find the highest or lowest.

Describe how NULL values affect the output of GREATEST and LEAST in PostgreSQL and how to handle them.

Consider what happens when you compare something unknown (NULL).