Sorting

Here is something that surprises a lot of beginners: a SQL query has no default sort order. The database returns rows in whatever order the execution plan happens to produce - run the same query twice and you might get a different sequence each time. If you care about order (and you usually do), you have to ask for it explicitly.

ORDER BY

SELECT name, salary
FROM employees
ORDER BY salary DESC;

ORDER BY goes at the end of your query, after WHERE and any grouping. Direction is straightforward:

ASC is the default, so you only need to write it when you want to be explicit.

Sorting by multiple columns

Multiple columns work left to right: the database sorts by the first column, then uses the second to break ties, and so on:

SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

This sorts departments A to Z, and within each department shows the highest-paid person first. The second column only matters when two rows tie on the first.

Sorting by expressions and aliases

You can sort by any expression, even one that is not in your SELECT clause:

SELECT name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

PostgreSQL lets you reference the alias directly in ORDER BY, which is convenient. You may also see people sort by column position - ORDER BY 1 meaning the first column in SELECT - but avoid it. Reorder your columns and the sort silently changes. Aliases are much safer.

How NULLs sort

NULL values have no natural position in a sort order - where does "unknown" rank? The SQL standard punts on this and leaves it to each database. In practice:

• PostgreSQL treats NULLs as larger than any other value, so they appear last in ASC and first in DESC.
• MySQL treats NULLs as smaller than any other value, so they appear first in ASC.

PostgreSQL gives you explicit control:

ORDER BY commission DESC NULLS LAST   -- NULLs after all non-NULL values
ORDER BY commission ASC NULLS FIRST   -- NULLs before all non-NULL values

Use whichever puts the NULLs where they make sense for the reader - "no commission" probably belongs at the bottom of a salary report.

LIMIT

LIMIT caps the number of rows returned:

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 10;

That gives you the 10 highest-paid employees. Without ORDER BY, LIMIT just grabs an arbitrary 10 rows - which is almost never what you actually want. Always pair them.

OFFSET

OFFSET skips rows before returning results. Paired with LIMIT, it gives you the classic pagination pattern:

-- Page 1: rows 1-10
SELECT name FROM employees ORDER BY id LIMIT 10 OFFSET 0;

-- Page 2: rows 11-20
SELECT name FROM employees ORDER BY id LIMIT 10 OFFSET 10;

-- Page 3: rows 21-30
SELECT name FROM employees ORDER BY id LIMIT 10 OFFSET 20;

One thing worth knowing now so it does not surprise you later: OFFSET does not actually skip work. The database still scans all those rows - it just discards them before returning results. On small tables that is fine. On large tables with high offsets, it gets slow fast. Production pagination systems usually use keyset pagination instead, filtering by the last-seen ID rather than counting rows from the top. That is an advanced technique, but keeping it in the back of your mind means you will recognize the pattern when you need it.