DISTINCT and Sorting

DISTINCT removes duplicate rows

SQL▶ Run
SELECT DISTINCT status
FROM orders
ORDER BY status;

Four statuses, not eight rows. DISTINCT collapses rows that are identical across every column you selected — not just the first one.

That distinction matters:

SQL▶ Run
SELECT DISTINCT country, signed_up
FROM customers
ORDER BY country;

This does not give one row per country. It gives one row per country plus signup date combination, which is almost certainly not what someone asking for "distinct countries" wanted.

NULL counts as a value here

SQL▶ Run
SELECT DISTINCT country
FROM customers
ORDER BY country;

One customer has no country, and DISTINCT keeps that as its own row. NULL is not equal to anything — including another NULL — yet DISTINCT and GROUP BY both treat all NULLs as one group. That inconsistency is deliberate in the standard and is worth remembering.

ORDER BY

Without it, a database may return rows in any order it likes. It usually looks stable on a small table and stops being stable the moment the data grows or the query plan changes.

If order matters, say so. There is no such thing as a default order.

SQL▶ Run
SELECT first_name, salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC;

ASC is ascending and the default; DESC is descending.

Sorting by more than one column

SQL▶ Run
SELECT dept_id, first_name, salary
FROM employees
WHERE dept_id IS NOT NULL
ORDER BY dept_id ASC, salary DESC;

Department ascending; within each department, salary descending. The order of the terms is the order of precedence.

Where do NULLs sort?

SQL▶ Run
SELECT first_name, salary
FROM employees
ORDER BY salary DESC;

The employee with no salary appears at one end. Which end is dialect-specific: PostgreSQL and Oracle put NULLs last in ASC and first in DESC; SQLite and MySQL treat NULL as smallest, so it comes first in ASC. PostgreSQL and Oracle let you say explicitly with ORDER BY salary DESC NULLS LAST. Do not rely on the default if it matters.

Common mistakes

• Assuming a default order. Rows come back in whatever order is cheapest.
• DISTINCT on the wrong column set. It applies to every selected column.
• SELECT DISTINCT to hide a duplicate-producing join. The duplicates mean your join is wrong; hiding them buries the bug.

Interview question

A query returns each customer twice after adding a join. A colleague adds DISTINCT. What is wrong with that?

DISTINCT treats a symptom. Duplicates after a join usually mean the join condition is incomplete and matches more rows than intended. Fix the join — otherwise any aggregate you compute later is still wrong.

Check yourself

1. Does SELECT DISTINCT a, b give distinct a values?
2. What order do rows come back in without ORDER BY?
3. Where do NULLs sort in PostgreSQL, and how do you control it?

Next

Next: Limiting results — LIMIT, OFFSET, and the equivalents in other databases.