# Filtering, Sorting and Limiting — PostgreSQL

Source: https://www.skillbyai.com/en/postgresql/q-select

> WHERE, ORDER BY, LIMIT.

## 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.

![Four ideas: filtering and sorting, joins, aggregation, CTEs.](assets/figures/postgresql/section-3-map.svg) — Figure 3.1 — Filters, joins, aggregates and CTEs.

## 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.

```sql
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.

**Quiz:** Is row order guaranteed without ORDER BY?

- [x] No
- [ ] Yes, by insertion order
- [ ] Yes, by primary key
- [ ] Only for small tables

*Answer:* No. Specify the order you need.