Sorting & Limiting Interview Questions & Answers

ORDER BY sorts the result set by one or more columns or expressions. It runs last in the logical pipeline (just before LIMIT), so it can reference SELECT aliases. Without it, row order is not guaranteed — the database may return rows in any order.

SELECT name, created_at
FROM users
ORDER BY created_at DESC;   -- newest first

Sorting happens after filtering and grouping, on the final rows. Large unindexed sorts can be expensive because the whole result must be ordered.

Rule of thumb: if you care about row order, you must say so with ORDER BY — never rely on insertion order.

Append ASC (ascending, the default) or DESC (descending) to each sort key. Direction is per column, so you can mix them.

SELECT name, age
FROM users
ORDER BY age DESC, name ASC;   -- oldest first; ties broken alphabetically

ASC is implicit, so ORDER BY age sorts ascending. Each key after the first only breaks ties left by the preceding keys.

Rule of thumb: list sort keys from most significant to least; each one is a tie-breaker for the ones before it.

List the columns comma-separated; the database sorts by the first, then breaks ties with the second, and so on. Order of the keys matters.

SELECT department, salary, name
FROM employees
ORDER BY department ASC, salary DESC;  -- group by dept, then highest paid first

This is how you get "grouped" looking output without aggregation — rows for the same department sit together, ordered within the department.

Rule of thumb: put the column you want grouped together first, the tie-breaker second.

Yes. Because ORDER BY runs after SELECT, you can sort by a computed expression, a column alias, or even a column position number (ORDER BY 2).

SELECT name, price * quantity AS total
FROM order_items
ORDER BY total DESC;        -- alias works here (unlike in WHERE)

Sorting by position (ORDER BY 2) is terse but fragile — reordering the SELECT list silently changes the sort. Prefer aliases for clarity.

Rule of thumb: sort by alias for readability; avoid positional ORDER BY numbers in production code.

It's dialect-dependent. Postgres/Oracle sort NULLs last in ASC (first in DESC); MySQL/SQL Server sort them first in ASC. The SQL standard lets you control it with NULLS FIRST / NULLS LAST.

-- Postgres/Oracle: force NULLs to the bottom regardless of direction
SELECT name, last_login
FROM users
ORDER BY last_login DESC NULLS LAST;

MySQL lacks NULLS LAST, so you emulate it: ORDER BY last_login IS NULL, last_login DESC.

Rule of thumb: if NULLs matter in your sort, state NULLS FIRST/LAST explicitly rather than trusting the default.

Use LIMIT n (Postgres/MySQL/SQLite), FETCH FIRST n ROWS ONLY (SQL standard / Oracle / DB2), or SELECT TOP n (SQL Server). It caps the result to the first n rows after ordering.

SELECT name, score
FROM players
ORDER BY score DESC
LIMIT 10;                  -- top 10 scorers

LIMIT is applied last, so it works on the sorted result. A LIMIT with no ORDER BY returns an arbitrary subset.

Rule of thumb: always pair LIMIT with ORDER BY for a deterministic "top N".

LIMIT n OFFSET m skips the first m rows and returns the next n — the classic page query. Page p (1-based, page size s) uses OFFSET (p - 1) * s.

-- page 3, 20 rows per page  ->  skip 40, take 20
SELECT id, title
FROM articles
ORDER BY published_at DESC, id DESC
LIMIT 20 OFFSET 40;

Always order by something unique (add id as a tie-breaker) so rows don't shift between pages. OFFSET still scans and discards the skipped rows, so deep pages get slow.

Rule of thumb: OFFSET is fine for early pages; for deep pagination use keyset pagination.

Keyset pagination fetches the next page by filtering past the last row seen instead of counting an offset. The database jumps straight to the position via an index, so cost stays constant no matter how deep you page.

-- next page after the last (published_at, id) you saw
SELECT id, title, published_at
FROM articles
WHERE (published_at, id) < ('2026-06-01 10:00', 5000)
ORDER BY published_at DESC, id DESC
LIMIT 20;

Unlike OFFSET, it doesn't scan-and-throw-away earlier rows, and it's stable when rows are inserted. The trade-off: you can't jump to an arbitrary page number.

Rule of thumb: use keyset pagination for infinite scroll / deep pages; OFFSET only for shallow, numbered pages.

OFFSET m doesn't skip rows for free — the database must generate and discard all m preceding rows to reach the page. At OFFSET 1000000, it produces a million rows just to throw them away.

-- reads ~100020 rows, returns 20 — the 100000 are wasted work
SELECT * FROM events ORDER BY created_at LIMIT 20 OFFSET 100000;

Cost grows linearly with the offset. Keyset pagination avoids this by seeking directly to the boundary with an indexed WHERE.

Rule of thumb: if page numbers reach the thousands, switch from OFFSET to keyset pagination.

A plain LIMIT caps the whole result, not per group. To get the top N within each group, rank rows inside each partition with a window function and filter.

-- top 3 highest-paid employees per department
SELECT *
FROM (
  SELECT name, department, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) ranked
WHERE rn <= 3;

Use ROW_NUMBER for an exact N, RANK/DENSE_RANK to include ties. Postgres also offers LATERAL joins for the same job.

Rule of thumb: "top N per group" means a window function, not LIMIT.

Use a CASE expression (or a lookup) in ORDER BY to map values to a sort rank — handy for non-alphabetical orderings like priority levels.

SELECT title, priority
FROM tickets
ORDER BY CASE priority
           WHEN 'high'   THEN 1
           WHEN 'medium' THEN 2
           WHEN 'low'    THEN 3
         END;

Without this, ORDER BY priority would sort alphabetically (high, low, medium) — rarely what you want. MySQL also has FIELD() as a shortcut.

Rule of thumb: encode custom sort orders with a CASE rank in ORDER BY.

When the sort keys aren't unique, rows with equal keys can come back in any order, and that order may differ between runs or pages. Adding a unique tie-breaker (like the primary key) makes the sort deterministic.

-- created_at ties resolved consistently by id
SELECT * FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

This matters most for pagination: without a stable order, the same row can appear on two pages or be skipped.

Rule of thumb: end every paginated ORDER BY with a unique column.

A plain LIMIT cuts off at exactly N rows, even if the (N+1)th ties the Nth. The standard FETCH FIRST n ROWS WITH TIES (Postgres 13+, SQL Server, Oracle) keeps all tied rows.

-- the top 3 scores, plus anyone tied with 3rd place
SELECT name, score
FROM players
ORDER BY score DESC
FETCH FIRST 3 ROWS WITH TIES;

It requires an ORDER BY (ties are defined by it). Without WITH TIES, you'd arbitrarily drop one of the tied players.

Rule of thumb: use WITH TIES when "top N" should never split a tie.

Postgres's DISTINCT ON (cols) keeps the first row per group as defined by ORDER BY. It's a concise way to grab the latest/largest row per key without a window function.

-- the most recent order per user
SELECT DISTINCT ON (user_id) user_id, id, created_at
FROM orders
ORDER BY user_id, created_at DESC;

The leading ORDER BY columns must start with the DISTINCT ON columns. Other databases achieve this with ROW_NUMBER() = 1.

Rule of thumb: DISTINCT ON is Postgres shorthand for "latest row per group."

A collation defines the rules for comparing and ordering text — case sensitivity, accent handling, and locale-specific alphabet order. Two databases with different collations can sort the same strings differently.

-- force a specific collation for this sort (Postgres)
SELECT name FROM users ORDER BY name COLLATE "de-DE-x-icu";

Collation affects ORDER BY, =/LIKE comparisons, and unique constraints. A mismatch between two columns being compared can even cause errors or prevent index use.

Rule of thumb: set collation deliberately when sorting human-readable text across locales.

Order by a random function: ORDER BY RANDOM() (Postgres/SQLite) or ORDER BY RAND() (MySQL). Combined with LIMIT, it samples random rows.

SELECT * FROM questions
ORDER BY RANDOM()
LIMIT 5;                 -- 5 random questions

This sorts the entire table by a random value first, so it's slow on large tables. For big tables, sample with a WHERE random() < 0.01 pre-filter or use TABLESAMPLE.

Rule of thumb: ORDER BY RANDOM() is fine for small tables; sample first on large ones.