PostgreSQL ORDER BY, LIMIT and DISTINCT

A table has no built-in order. If order matters, say so with ORDER BY. Sort by one or many columns, each ASC (default) or DESC. In PostgreSQL, NULLs sort as if they were larger than every value (last in ASC, first in DESC); change that with NULLS FIRST / NULLS LAST.

LIMIT n returns at most n rows and OFFSET m skips m rows first, which is the classic way to page results. The SQL-standard spelling FETCH FIRST n ROWS ONLY does the same.

DISTINCT removes duplicate result rows. DISTINCT ON (expr) is a PostgreSQL extra: it keeps the first row of each group according to ORDER BY, which makes “latest row per X” queries short.

Syntax

SELECT [DISTINCT | DISTINCT ON (expr)] columns
FROM table
ORDER BY col1 [ASC|DESC] [NULLS FIRST|LAST], col2 ...
LIMIT n OFFSET m;

Examples

Edit the code and run it. Output is printed the way psql prints it.

5.1Most expensive first

1
2
3

SELECT name, price
FROM products
ORDER BY price DESC;

DESC sorts from high to low.

5.2Sort by several columns

1
2
3

SELECT category, name, price
FROM products
ORDER BY category ASC, price DESC;

Rows are grouped alphabetically by category; within each category, the most expensive comes first.

5.3Where do NULLs go?

1
2

SELECT name, city FROM customers ORDER BY city;
SELECT name, city FROM customers ORDER BY city NULLS FIRST;

Mia has no city. By default she appears last in ascending order; NULLS FIRST moves her to the top.

5.4Paging with LIMIT and OFFSET

-- page 2, 5 rows per page
SELECT id, name, price
FROM products
ORDER BY id
LIMIT 5 OFFSET 5;

Skips rows 1–5 and returns rows 6–10. Always pair LIMIT with ORDER BY, otherwise “page 2” can change between runs. For very large tables, “keyset pagination” (WHERE id > last_seen_id ORDER BY id LIMIT 5) is faster than big offsets.

5.5DISTINCT and DISTINCT ON

SELECT DISTINCT country FROM customers ORDER BY country;

-- latest order of each customer
SELECT DISTINCT ON (customer_id) customer_id, id AS order_id, order_date, status
FROM orders
ORDER BY customer_id, order_date DESC;

The first query lists each country once. In the second, rows are sorted by customer and then newest date first; DISTINCT ON (customer_id) keeps only the first row per customer, i.e. their latest order. The ORDER BY must start with the DISTINCT ON expression.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 5.1

Show the name and price of the 3 most expensive products, most expensive first.

Hint

ORDER BY ... DESC LIMIT 3

Solution

SELECT name, price FROM products ORDER BY price DESC LIMIT 3;

Exercise 5.2

List every distinct category of product in alphabetical order.

Hint

SELECT DISTINCT ... ORDER BY

Solution

SELECT DISTINCT category FROM products ORDER BY category;

Exercise 5.3

For each department id, show the highest-paid employee: dept_id, first_name, salary. Ignore employees with no department. Order by dept_id.

Hint

DISTINCT ON (dept_id) with ORDER BY dept_id, salary DESC

Solution

SELECT DISTINCT ON (dept_id) dept_id, first_name, salary FROM employees WHERE dept_id IS NOT NULL ORDER BY dept_id, salary DESC;