SELECT: filtering, sorting and paging

conceptThe concept

The logical order is: FROM (and joins) → WHERE → GROUP BY → HAVING → SELECT list (expressions and aliases) → DISTINCT → ORDER BY → LIMIT / OFFSET. That is why a column alias defined in the SELECT list can be used in ORDER BY but not in WHERE.

Tables are unordered sets. Without ORDER BY, PostgreSQL returns rows in whatever order the plan produces them, which can change after an UPDATE, a VACUUM or a different plan. If the order matters, say so, and add a unique column as a tiebreaker.

LIKE is case-sensitive pattern matching with % (any string) and _ (one character); ILIKE ignores case. FETCH FIRST n ROWS WITH TIES also returns rows that tie with the last one on the ORDER BY key.

whyWhy it matters

Most "random" bugs in reports and APIs come from paging without a stable sort: a row appears on two pages, or never. OFFSET paging also gets slower with every page because the server still reads and discards the skipped rows; the performance level shows keyset pagination as the fix.

Use caseReal-world scenario

An admin screen lists gold and platinum customers in Germany, sorted by name, 10 per page. The query below is exactly that, and adding id to the ORDER BY makes the paging stable when two customers share a name.

Try itExamples

Run and edit these in the interactive lesson.

SELECT id, name, city, tier
FROM customers
WHERE country = 'DE' AND tier <> 'standard'
ORDER BY name, id
LIMIT 10;

SELECT sku, name, category, price
FROM products
WHERE category IN ('books', 'toys')
  AND price BETWEEN 20 AND 120
  AND name ILIKE '%guide%' OR name ILIKE '%puzzle%'
ORDER BY price DESC;

SELECT sku, name, category, price
FROM products
WHERE category IN ('books', 'toys')
  AND price BETWEEN 20 AND 120
  AND (name ILIKE '%guide%' OR name ILIKE '%puzzle%')
ORDER BY price DESC;

An alias from the SELECT list works in ORDER BY. WITH TIES keeps going while the price equals the last returned row.

SELECT name, price, round(price * 0.9, 2) AS sale_price
FROM products
ORDER BY sale_price DESC
FETCH FIRST 5 ROWS WITH TIES;

SELECT DISTINCT country, city
FROM customers
ORDER BY country, city;

Avoid / preferDo this, not that

SELECT name, price FROM products
WHERE category = 'books' OR category = 'toys' AND price < 20;

SELECT name, price FROM products
WHERE category IN ('books', 'toys') AND price < 20;

SELECT id, created_at FROM orders LIMIT 10;

SELECT id, created_at FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 10;

PitfallsCommon mistakes

• Using a SELECT alias in WHERE (WHERE sale_price > 10). WHERE runs before the SELECT list is computed; repeat the expression or use a subquery.
• Mixing AND and OR without parentheses. AND has higher precedence, which silently changes which rows match.
• Paging with LIMIT/OFFSET on a non-unique sort key, so rows shift between pages.

LimitsWhen not to use it

Avoid SELECT * in application code: it fetches columns you do not need (including large TOASTed ones), breaks when columns are added or reordered, and prevents index-only scans. Use it freely when exploring in psql.

SecuritySecurity notes

Never build a WHERE clause by concatenating user input into the SQL string. Use bind parameters (WHERE email = $1) from your driver; that one habit prevents SQL injection.

ChallengeTry it yourself

SELECT name, price
FROM products
WHERE category = 'books' AND active AND price < 100
ORDER BY price DESC, name
LIMIT 5;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• SELECT
• Comparison operators and NULL