Filtering, Sorting and Limiting

Return only what you need

Select specific columns rather than *, filter with WHERE, sort with ORDER BY, and page with LIMIT (plus keyset pagination for large result sets: WHERE id > last_seen ORDER BY id LIMIT 50 scales better than large OFFSETs). Without ORDER BY, row order is not guaranteed, even if it looks stable in testing.

Ask precise questions

Filtering, joining, grouping and composing queries cover most application needs.

The two most expensive products under 1000, run

I ran this with psql against PostgreSQL 16.2 (a local server started with the pgserver Python package), on a fresh database loaded with the shop schema from the first section. Filtering by price, sorting descending and limiting to 2 returns the fountain pen and the notebook.

SELECT name, price
FROM products
WHERE price < 1000
ORDER BY price DESC
LIMIT 2;

Output:

     name     | price  
--------------+--------
 Fountain pen | 899.00
 Notebook     | 120.00
(2 rows)

Always ORDER BY before LIMIT

LIMIT without ORDER BY returns arbitrary rows; results can change between runs or versions.

Quick check: Is row order guaranteed without ORDER BY?

• No
• Yes, by insertion order
• Yes, by primary key
• Only for small tables