SELECT, Filtering & Sorting

WHERE, ORDER BY, LIMIT, computed columns — and DISTINCT ON, a PostgreSQL favourite.

Every query starts with SELECT. This lesson covers the parts used in nearly every query: choosing and computing columns, filtering rows, sorting and limiting — plus DISTINCT ON, which solves the common 'top row per group' problem in one line.

Queries on the sample data

WHERE filters rows before anything else; ORDER BY salary DESC sorts highest first.

round(salary / 12, 2) and age() compute new columns; AS names them.

LIMIT 3 keeps the first three rows after sorting.

DISTINCT ON (dept_id) keeps the first row of each department, where 'first' is decided by ORDER BY dept_id, salary DESC.

Order of evaluation

SELECT name, salary * 12 AS yearly FROM employees WHERE yearly > 2000000;

SELECT name, salary * 12 AS yearly FROM employees WHERE salary * 12 > 2000000;

WHERE runs before SELECT, so the alias does not exist yet. Repeat the expression or wrap the query in a subquery/CTE.

SELECT * FROM employees LIMIT 10 OFFSET 100000;

SELECT * FROM employees WHERE emp_id > $last_seen ORDER BY emp_id LIMIT 10;

Large OFFSETs still read and discard all skipped rows. Keyset pagination stays fast on any page.

What does DISTINCT ON (a) require of ORDER BY?

ORDER BY must start with the same expressions (a), followed by the ordering that decides which row of each group is kept.

Why can you use a SELECT alias in ORDER BY but not in WHERE?

ORDER BY is evaluated after the SELECT list, WHERE before it.