Sorting, LIMIT and DISTINCT

The database returns rows in no guaranteed order. To sort the result, take only the first few rows or remove duplicates, there are three commands.

ORDER BY — sorting

SELECT first_name, last_name, salary FROM teachers ORDER BY salary;

By default it sorts ascending (ASC). For descending order use DESC:

SELECT first_name, last_name, salary FROM teachers ORDER BY salary DESC;

By several columns: first by the first one, and on a tie by the second.

SELECT last_name, first_name, course FROM students ORDER BY course DESC, last_name;

LIMIT — only the first N rows

“The 3 best-paid teachers”:

SELECT first_name, last_name, salary FROM teachers ORDER BY salary DESC LIMIT 3;

Always use LIMIT together with ORDER BY — otherwise “which 3” you get is down to chance.

For the next “page” there is OFFSET: LIMIT 3 OFFSET 3 gives rows 4, 5 and 6.

DISTINCT — without repeats

Which cities do the students come from?

SELECT DISTINCT city FROM students ORDER BY city;

DISTINCT applies to the whole row: SELECT DISTINCT city, course gives the different city–year pairs.

Where does NULL go?

PostgreSQL treats NULL as the largest value when sorting: it comes last in ascending order and first with DESC. You can change that: ORDER BY scholarship DESC NULLS LAST.

The order of the parts

The parts of a query are always written in this order:

SELECT … FROM … WHERE … ORDER BY … LIMIT …;

Remember

• ORDER BY column [DESC] sorts; several columns are separated by commas.
• LIMIT n gives the first n rows; always with ORDER BY.
• DISTINCT removes duplicates.