SELECT, DISTINCT, ORDER BY and LIMIT

Every SQL interview opens here, and the questions are easy marks — provided you know the one thing that separates people who have written SQL from people who have memorised it:

the order clauses are written is not the order they run.

Logical execution order

This single list explains most of the errors people hit:

• An alias defined in `SELECT` cannot be used in `WHERE` — WHERE ran first, the

alias does not exist yet. It can be used in ORDER BY, which runs after.

• `WHERE` cannot contain an aggregate — grouping has not happened. That is

`HAVING`.

• `DISTINCT` applies to the whole row, not to one column, because it runs on

SELECT's output.

Being able to recite that order is a common interview question in its own right.

SELECT and aliases

SELECT
    first_name AS name,
    salary * 12 AS annual_salary
FROM employees;

AS is optional but keep it — salary * 12 annual_salary is valid and unreadable, and a missing comma silently turns a column into an alias for the previous one, which is a real bug that a habit of writing AS prevents.

Avoid SELECT * in anything but exploration. It breaks when columns are added, ships data nobody needs, and prevents index-only scans.

DISTINCT

SELECT DISTINCT department FROM employees;

-- DISTINCT applies to the ROW, so this returns every unique PAIR,
-- not unique departments:
SELECT DISTINCT department, job_title FROM employees;

For a count of unique values, COUNT(DISTINCT column) — and note that it ignores NULLs, which is the trap in that question.

SELECT COUNT(DISTINCT department) AS departments FROM employees;

If you need distinct values of one column but other columns alongside, DISTINCT is the wrong tool — that is `ROW_NUMBER()` with a filter, or GROUP BY.

ORDER BY

SELECT first_name, salary
FROM employees
ORDER BY salary DESC, first_name ASC;

ASC is the default. Sorting by several columns applies them left to right — the second only breaks ties in the first. Two things worth knowing:

NULL placement is not standardised. PostgreSQL and Oracle sort NULLs last ascending;

MySQL and SQL Server sort them first. Be explicit when it matters:

ORDER BY commission DESC NULLS LAST;         -- PostgreSQL, Oracle
ORDER BY commission IS NULL, commission DESC; -- MySQL equivalent

`ORDER BY 2` sorts by the second output column. It works, and it is a maintenance

hazard — one inserted column silently changes the sort. Name the column.

LIMIT and pagination

SELECT first_name, salary FROM employees
ORDER BY salary DESC
LIMIT 10;              -- MySQL, PostgreSQL, SQLite

SELECT TOP 10 ... ;                        -- SQL Server
... FETCH FIRST 10 ROWS ONLY;              -- Oracle 12c+, standard SQL

`LIMIT` without `ORDER BY` is meaningless. There is no default row order in SQL, so

"the first 10" is whatever the engine happens to produce — a genuinely non-deterministic query, and a common interview follow-up.

OFFSET paginates:

SELECT first_name FROM employees
ORDER BY employee_id
LIMIT 10 OFFSET 20;    -- rows 21-30

Be ready for the scaling question: OFFSET 100000 still makes the database produce and discard 100,000 rows. Keyset pagination — `WHERE employee_id > :last_seen_id ORDER BY employee_id LIMIT 10` — stays fast at any depth because an index takes it straight to the right place.

The Nth highest problem

The classic, and it has two good answers:

-- With LIMIT/OFFSET: skip N-1 rows.
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;                -- second highest

-- With a window function, which handles ties explicitly:
SELECT salary FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 2;

Ask which behaviour is wanted when two people share the top salary — the ranking version makes the choice explicit, which is why it is the stronger answer.

Mistakes that cost the interview

• Using a `SELECT` alias in `WHERE`. Repeat the expression, or wrap the query.
• `LIMIT` with no `ORDER BY`, giving a non-deterministic result.
• Expecting `DISTINCT` to apply to one column in a multi-column select.
• Forgetting `COUNT(DISTINCT x)` skips NULLs.
• `ORDER BY` by position number in production code.

Practise these on the SQL sheet, which answers each question inline.