Sorting and Limiting

By default, a SQL query returns rows in no guaranteed order. The database delivers them in whatever order is most efficient for the execution plan it chose. If you ran the same query twice, you might get rows in a different sequence each time. If you care about the order of results, and you usually do, you have to ask for it explicitly with ORDER BY.

ORDER BY

SELECT name, salary
FROM employees
ORDER BY salary DESC;

Place ORDER BY at the end of your query, after WHERE and any grouping. The direction options are:

Direction	Keyword	Behavior
Ascending	ASC	Smallest to largest, A to Z, earliest to latest
Descending	DESC	Largest to smallest, Z to A, latest to earliest

ASC is the default. If you omit the direction, the database sorts ascending.

Sorting by multiple columns

When you provide multiple columns to ORDER BY, the database sorts by the first column, then breaks ties using the second column, and so on down the list:

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

This groups employees by department alphabetically, and within each department, lists the highest-paid employee first. The secondary sort only kicks in when two rows have the same value in the primary sort column.

Sorting by expressions and aliases

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

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

PostgreSQL allows referencing the alias in ORDER BY, which is convenient. You can also sort by column position (the number 1 refers to the first column in SELECT, 2 to the second, and so on), but this is fragile: if you reorder the columns in SELECT, the sort changes silently. Prefer aliases or expressions over positional references.

How NULLs sort

NULL values have no natural position in a sort order because they represent unknown values. The SQL standard leaves this behavior 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

This is useful when NULLs represent a meaningful absence and you want to control whether they surface at the top or bottom of your results.

LIMIT

LIMIT caps the number of rows returned:

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

This gives you the 10 highest-paid employees. Without ORDER BY, LIMIT gives you an arbitrary 10 rows, which is almost never what you want. Pair them together whenever the specific rows matter.

OFFSET

OFFSET skips rows before starting to return results. Combined with LIMIT, it is the classic pattern for pagination:

-- 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;

There is a performance consideration worth knowing for later: OFFSET requires the database to scan and discard all the skipped rows, which gets progressively slower on large tables as the offset grows. For production pagination on large datasets, keyset pagination (filtering by the last-seen ID rather than using OFFSET) is much faster. That technique is covered in more advanced material, but keeping it in mind now will save you from a performance surprise later.