ORDER BY
Sorting, NULLs, Tiebreakers, and Index-Backed Scans
LinkedIn Hook
"Your pagination showed the same user on page 1 and page 3. Your ORDER BY had no tiebreaker."
Most developers treat ORDER BY as the sentence that ends a query — a throwaway clause you tack on when "the list looks wrong." Then pagination skips rows. Then NULLs show up in the wrong place. Then the query that used to take 4 ms on a 10k-row dev database takes 12 seconds in production because the planner switched from an index scan to an external merge sort on disk.
ORDER BY is one of the most deceptively technical clauses in SQL. The default direction is ASC, but the default NULL position differs between Postgres and MySQL. ORDER BY can sort by an expression, an alias, or a column position number. It interacts with GROUP BY, with LIMIT, and — most importantly — with indexes. A matching index turns a sort into a free ordered walk. A non-matching index forces the database to buffer the entire result set and sort it in memory or spill to disk.
And the single bug that eats the most engineering hours: pagination without a deterministic tiebreaker. ORDER BY created_at LIMIT 20 OFFSET 40 looks fine until two rows share the same timestamp, at which point your users see duplicates, miss rows, and file tickets you cannot reproduce locally.
In Lesson 4.6, I break down ORDER BY end to end: ASC/DESC, NULL positioning across engines, multi-column tiebreakers, ORDER BY on expressions and column positions, the interaction with GROUP BY and LIMIT, and how to read EXPLAIN to tell whether your index is actually being used for the sort.
Read the full lesson -> [link]
#SQL #Database #PostgreSQL #MySQL #BackendDevelopment #InterviewPrep #QueryPerformance
What You'll Learn
- How ASC and DESC control sort direction and why ASC is the default
- Where NULLs land by default in Postgres vs MySQL, and how to override with NULLS FIRST / NULLS LAST
- How multi-column ORDER BY works as a lexicographic tiebreaker chain
- ORDER BY on expressions, aliases from the SELECT list, and column positions (ORDER BY 2)
- Why deterministic pagination requires a stable tiebreaker on a unique column
- How ORDER BY interacts with GROUP BY — and why unsorted GROUP BY output is not guaranteed
- How to tell from EXPLAIN whether an index scan is serving the sort or a Sort node is doing the work
- Why ORDER BY + LIMIT with a matching index is one of the fastest patterns in SQL
The Filing Cabinet Analogy — Primary by Year, Then Alphabetical
Picture a stack of manila file folders dumped on a desk. Your job: organize them first by the year printed on the tab, oldest to newest, and within each year, alphabetically by the client name. That is exactly what ORDER BY year ASC, client_name ASC does. The first column is the primary sort key — every folder is grouped by year. The second column is the tiebreaker — inside each year, folders are arranged alphabetically.
Now suppose two folders have the same year and the same client name. Without a third sort key, you have no rule to decide which one comes first. You could grab either one, and a different person sorting the same stack might grab them in the opposite order. That is exactly what happens in SQL when your ORDER BY is not deterministic: the engine is free to return tied rows in any order it likes, and the order may change between runs, between replicas, or after an index rebuild.
The fix is the same in both worlds. Add a final tiebreaker that is guaranteed unique — a case number for folders, a primary key for rows. With a unique last key, the sort becomes a total order: every item has exactly one correct position, and every sorter produces the same result.
+---------------------------------------------------------------+
| THE FILING CABINET SORT |
+---------------------------------------------------------------+
| |
| UNSORTED STACK ORDER BY year, name, id |
| +-----------+ +--------------------------+ |
| | 2024 Bob | | 2023 Alice id=17 | |
| | 2023 Dave | -> | 2023 Dave id=04 | |
| | 2023 Alice| | 2024 Bob id=22 | |
| | 2024 Bob | | 2024 Bob id=31 | |
| +-----------+ +--------------------------+ |
| ^^^^ ^^^^^ ^^^^ |
| year name tiebreaker |
| |
+---------------------------------------------------------------+ASC vs DESC — Defaults and Syntax
ORDER BY sorts rows by one or more keys. Each key can be followed by ASC (ascending, smallest to largest) or DESC (descending, largest to smallest). ASC is the default in every major SQL engine, so ORDER BY created_at is identical to ORDER BY created_at ASC.
-- Sample table used throughout this lesson
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer TEXT,
total NUMERIC,
status TEXT,
created_at TIMESTAMPTZ
);
INSERT INTO orders (customer, total, status, created_at) VALUES
('Alice', 120.00, 'shipped', '2026-04-10 09:00'),
('Bob', 55.50, 'pending', '2026-04-11 14:30'),
('Carol', 300.00, 'shipped', '2026-04-11 14:30'),
('Dave', 42.00, NULL, '2026-04-12 08:15'),
('Eve', 999.00, 'shipped', '2026-04-09 16:45');
-- ASC (default): smallest total first
SELECT customer, total FROM orders ORDER BY total;
-- customer | total
-- ---------+-------
-- Dave | 42.00
-- Bob | 55.50
-- Alice | 120.00
-- Carol | 300.00
-- Eve | 999.00
-- DESC: largest total first
SELECT customer, total FROM orders ORDER BY total DESC;
-- customer | total
-- ---------+-------
-- Eve | 999.00
-- Carol | 300.00
-- Alice | 120.00
-- Bob | 55.50
-- Dave | 42.00Direction is per column — you can mix ASC and DESC freely in a multi-column ORDER BY. ORDER BY status ASC, created_at DESC sorts by status alphabetically, then within each status shows newest orders first.
NULL Handling — Where Do NULLs Land?
NULLs are neither smaller nor larger than any real value, so every database has to pick a convention for where to put them. The convention is not the same across engines, and this is one of the most common portability bugs in SQL.
+---------------------------------------------------------------+
| DEFAULT NULL POSITION |
+---------------------------------------------------------------+
| |
| ENGINE | ASC default | DESC default |
| -------------+----------------+------------------ |
| PostgreSQL | NULLS LAST | NULLS FIRST |
| Oracle | NULLS LAST | NULLS FIRST |
| MySQL | NULLS FIRST | NULLS LAST |
| SQL Server | NULLS FIRST | NULLS LAST |
| SQLite | NULLS FIRST | NULLS LAST |
| |
| Rule of thumb: Postgres treats NULL as "larger than any |
| value", MySQL/SQL Server treat it as "smaller than any |
| value". Never rely on the default — be explicit. |
| |
+---------------------------------------------------------------+In Postgres you can override the default with NULLS FIRST or NULLS LAST. MySQL 8.0 does not support the NULLS FIRST/NULLS LAST syntax directly and requires a workaround using IS NULL as a first sort key.
-- PostgreSQL: explicit NULL position
SELECT customer, status FROM orders
ORDER BY status ASC NULLS FIRST;
-- customer | status
-- ---------+---------
-- Dave | NULL <- pulled to the top
-- Bob | pending
-- Alice | shipped
-- Carol | shipped
-- Eve | shipped
SELECT customer, status FROM orders
ORDER BY status ASC NULLS LAST; -- the Postgres ASC default
-- customer | status
-- ---------+---------
-- Bob | pending
-- Alice | shipped
-- Carol | shipped
-- Eve | shipped
-- Dave | NULL <- pushed to the bottom
-- MySQL workaround (no NULLS FIRST/LAST keyword):
-- IS NULL returns 0 for non-null, 1 for null.
-- Sorting on it pushes NULLs to one end first.
SELECT customer, status FROM orders
ORDER BY status IS NULL, status ASC; -- NULLs LAST
SELECT customer, status FROM orders
ORDER BY status IS NOT NULL, status ASC; -- NULLs FIRSTThe safe rule: if you care where NULLs land, say so explicitly. Do not rely on the engine default, because the same query ported to another database will silently reshuffle your output.
Multi-Column Sort and the Tiebreaker Chain
When two rows are equal on the first key, the database moves to the second key. When they are equal on the second, it moves to the third. This is a lexicographic (dictionary) order, and the chain continues until one of the keys differs — or until there are no more keys, in which case the order is undefined.
-- Two orders have identical created_at (Bob and Carol at 14:30).
-- Without a tiebreaker, their relative order is implementation-defined.
SELECT customer, created_at FROM orders
ORDER BY created_at ASC;
-- customer | created_at
-- ---------+--------------------
-- Eve | 2026-04-09 16:45
-- Alice | 2026-04-10 09:00
-- Bob | 2026-04-11 14:30 <- tied
-- Carol | 2026-04-11 14:30 <- tied, could be first
-- Dave | 2026-04-12 08:15
-- Add a tiebreaker on the primary key.
-- Now the order is fully deterministic.
SELECT customer, created_at, id FROM orders
ORDER BY created_at ASC, id ASC;
-- customer | created_at | id
-- ---------+---------------------+----
-- Eve | 2026-04-09 16:45 | 5
-- Alice | 2026-04-10 09:00 | 1
-- Bob | 2026-04-11 14:30 | 2 <- id=2 < id=3
-- Carol | 2026-04-11 14:30 | 3
-- Dave | 2026-04-12 08:15 | 4Stability is not guaranteed in SQL. Unlike some in-memory sorting algorithms, SQL does not promise that rows with equal keys keep their original insertion order. The only way to make the sort reproducible is to add enough tiebreakers that no two rows can tie on all of them — and the simplest way to do that is to append the primary key as the final sort key.
ORDER BY on Expressions, Aliases, and Column Positions
ORDER BY accepts much more than just column names. You can sort by an arbitrary expression, by an alias declared in the SELECT list, or by a positional number referring to a column in the SELECT list.
-- Expression: sort by a computed value
SELECT customer, total, total * 0.08 AS tax
FROM orders
ORDER BY total * 0.08 DESC;
-- Same result as ordering by total DESC, since the expression is monotonic.
-- Alias: ORDER BY can reference a name introduced in SELECT
-- (WHERE and GROUP BY cannot do this — only ORDER BY and HAVING in some dialects).
SELECT customer, total * 0.08 AS tax
FROM orders
ORDER BY tax DESC;
-- customer | tax
-- ---------+--------
-- Eve | 79.92
-- Carol | 24.00
-- Alice | 9.60
-- Bob | 4.44
-- Dave | 3.36
-- Column position: ORDER BY 2 means "the second column in the SELECT list"
SELECT customer, total FROM orders
ORDER BY 2 DESC;
-- Equivalent to ORDER BY total DESC.
-- Useful for ad-hoc queries, dangerous in production code:
-- if someone adds a column to SELECT, the position shifts and the
-- sort silently changes meaning.Best practice: use column names or aliases in production code. Positional ORDER BY is a convenience for interactive SQL, not a contract you want your application code to rely on. A future developer adding a column to the SELECT list should not accidentally break your sort.
ORDER BY with GROUP BY
A common misconception: GROUP BY sorts its output. It does not. GROUP BY guarantees one row per group, but the order in which those groups come back is unspecified. If you want a sorted grouped result, you must add an explicit ORDER BY.
-- Count orders per status, sorted by count descending
SELECT status, COUNT(*) AS n
FROM orders
GROUP BY status
ORDER BY n DESC, status ASC; -- tiebreaker on status for determinism
-- status | n
-- ---------+---
-- shipped | 3
-- pending | 1
-- NULL | 1
-- You can ORDER BY an aggregate directly
SELECT customer, SUM(total) AS revenue
FROM orders
GROUP BY customer
ORDER BY SUM(total) DESC
LIMIT 3;
-- customer | revenue
-- ---------+---------
-- Eve | 999.00
-- Carol | 300.00
-- Alice | 120.00The rule: the only way to guarantee the order of rows coming out of any SQL query is to write an ORDER BY. Everything else — GROUP BY order, "it always came back sorted in my tests," "the index is on this column" — is implementation detail that can change between versions and execution plans.
Index-Backed Sorts vs Sort Nodes
This is the performance heart of ORDER BY. When the database has an index whose key order matches your ORDER BY, it can walk the index in order and return rows without doing any sorting work at all. When the index order does not match, the database must read all qualifying rows, buffer them, and sort them — either in memory (fast) or by spilling to disk via external merge sort (slow, sometimes dramatically so).
-- Create an index sorted by created_at DESC
CREATE INDEX idx_orders_created_desc ON orders (created_at DESC);
-- Query matches the index direction exactly: free ordered walk
EXPLAIN ANALYZE
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 10;
-- Limit (cost=0.15..0.50 rows=10)
-- -> Index Scan using idx_orders_created_desc on orders
-- (actual rows=5 loops=1)
-- NO "Sort" node! The planner reads the index top-down and stops after 10 rows.
-- Query asks for the opposite direction: Postgres can scan the same
-- index backward and still avoid a Sort node.
EXPLAIN ANALYZE
SELECT * FROM orders
ORDER BY created_at ASC
LIMIT 10;
-- Limit (cost=0.15..0.50 rows=10)
-- -> Index Scan Backward using idx_orders_created_desc on orders
-- Query with a non-indexed sort key: planner inserts a Sort node
EXPLAIN ANALYZE
SELECT * FROM orders
ORDER BY customer ASC
LIMIT 10;
-- Limit (cost=1.11..1.12 rows=10)
-- -> Sort (cost=1.11..1.12 rows=5 width=64)
-- Sort Key: customer
-- Sort Method: quicksort Memory: 25kB
-- -> Seq Scan on ordersThe key signal in EXPLAIN is whether you see a Sort node. No Sort node means the index served the order for free. A Sort node means the database buffered and sorted, and the Sort Method line tells you whether it fit in memory (quicksort, fast) or spilled (external merge Disk: 128MB, slow).
+---------------------------------------------------------------+
| ORDER BY EXECUTION SHAPES |
+---------------------------------------------------------------+
| |
| INDEX MATCHES SORT KEY: |
| Seq Scan [X] |
| Index Scan [OK] -> ordered walk, no sort cost |
| LIMIT 10 -> stops after 10 index entries, O(log N) |
| |
| INDEX DOES NOT MATCH: |
| Seq Scan -> Sort -> Limit |
| Must read entire matching set, sort it, then truncate. |
| LIMIT does not help avoid the sort, only reduces output. |
| |
| RULE: |
| If ORDER BY + LIMIT is hot, create an index whose leading |
| columns match the ORDER BY keys (including direction for |
| mixed ASC/DESC). |
| |
+---------------------------------------------------------------+For mixed-direction multi-column sorts like ORDER BY status ASC, created_at DESC, Postgres can only use an index whose column directions match (or are exactly reversed). A plain CREATE INDEX ... (status, created_at) sorts both ASC and will force a Sort node; CREATE INDEX ... (status ASC, created_at DESC) serves the query directly.
Pagination — Why Deterministic Order Matters
The most common real-world ORDER BY bug is pagination without a tiebreaker. Consider:
-- Page 1
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 0;
-- Page 2
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 20;If any two orders share the exact same created_at, the engine is free to return them in either order on any given run. The sort is not stable, so page 1 might put order X at position 20 and page 2 might put the same order X at position 21 — user sees duplicate. Or page 1 puts X at position 20 and page 2 skips X entirely — user misses a row. These bugs are extremely hard to reproduce because they depend on execution plan, parallel workers, and data layout on disk.
-- Always append a unique tiebreaker (the primary key)
SELECT * FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 0;
-- For even better pagination performance, use keyset pagination
-- instead of OFFSET. It also requires a deterministic ORDER BY.
SELECT * FROM orders
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;Napkin AI Visual Prompt: "Dark navy gradient (#0a0f1f -> #111a2e). Two side-by-side execution diagrams. LEFT box labeled 'No Tiebreaker -> Broken Pagination' shows rows A, B, C with B and C sharing a timestamp, with rose (#ff5c8a) arrows showing C appearing on page 1 AND page 2. RIGHT box labeled 'Primary Key Tiebreaker -> Deterministic' shows the same rows with sky blue (#4fc3f7) arrows, each row appearing exactly once across pages. Below both, a small EXPLAIN comparison: rose Sort node vs sky blue Index Scan. White monospace labels throughout."
Common Mistakes
1. Paginating without a deterministic tiebreaker.
ORDER BY created_at LIMIT 20 OFFSET 40 looks innocent until two rows share the same timestamp. The engine is not required to return tied rows in a stable order, so pagination produces duplicates, skips, and phantom rows. Always append the primary key (or another guaranteed-unique column) as the final sort key: ORDER BY created_at, id. This makes the total order deterministic and pagination reproducible.
2. Relying on default NULL position.
Postgres puts NULLs last under ASC by default; MySQL and SQL Server put them first. Code you wrote for one engine produces a different row order when ported to another, and the difference is silent — no error, just a different top row. If NULL position matters, be explicit: ORDER BY col ASC NULLS LAST on Postgres, and the IS NULL trick on MySQL.
3. Sorting huge result sets without a matching index.
An ORDER BY with no supporting index forces the database to buffer every qualifying row and sort it. On large tables this produces an external merge sort that spills to disk, runs for seconds or minutes, and hammers the I/O subsystem. Always check the plan for a Sort node with Sort Method: external merge and add an index whose leading columns match the ORDER BY when the query is hot.
4. Assuming GROUP BY returns sorted output. GROUP BY only guarantees "one row per group" — it says nothing about the order of those groups. If you need sorted output, add an explicit ORDER BY after GROUP BY. Older MySQL versions sometimes returned grouped output in grouping-key order and code came to depend on it; newer versions removed that implicit sort and quietly broke a generation of queries.
5. Using positional ORDER BY in application code.
ORDER BY 2 DESC is fine at the psql prompt but dangerous in code. The next developer who adds a column to the SELECT list shifts every position and silently reinterprets your sort. Use column names or aliases in anything that ships.
Interview Questions
1. "Where do NULLs sort by default in PostgreSQL and in MySQL, and how do you control it?"
In PostgreSQL, NULLs sort LAST for ASC and FIRST for DESC by default — the convention is that NULL is "larger than any real value." In MySQL and SQL Server, NULLs sort FIRST for ASC and LAST for DESC — NULL is treated as "smaller than any real value." Postgres lets you override explicitly with ORDER BY col ASC NULLS FIRST or ORDER BY col DESC NULLS LAST. MySQL 8.0 does not support the NULLS FIRST/NULLS LAST keywords, so the standard workaround is to add a leading sort expression using IS NULL: ORDER BY col IS NULL, col ASC pushes NULLs to the end, while ORDER BY col IS NOT NULL, col ASC brings them to the front. The broader lesson: never rely on the default NULL position if your code might run against more than one engine — be explicit every time.
2. "How does the database decide whether to use an index to satisfy an ORDER BY, and how can you tell from EXPLAIN?"
The planner can use an index to serve ORDER BY without sorting when the index's key order matches the ORDER BY keys — either exactly, or exactly reversed (in which case Postgres walks the index backward). If the ORDER BY mixes ASC and DESC across columns, the index must also have that exact mixed direction, otherwise it cannot satisfy the order. The planner also considers whether the query can be answered entirely from the index or needs to fetch heap rows. The way to confirm this in EXPLAIN is to look for the absence of a Sort node: if you see Index Scan (or Index Scan Backward) directly feeding the Limit or query output, the sort was served by the index for free. If you see a Sort node with a Sort Key: line, the database had to buffer and sort the result set; the Sort Method line tells you whether it happened in memory (quicksort) or on disk (external merge). For a query with ORDER BY created_at DESC LIMIT 10 against a 100M-row table, the difference between an index-backed scan and a disk spill can be six orders of magnitude in latency.
3. "Explain why pagination with ORDER BY created_at LIMIT OFFSET can return duplicates or skip rows, and how you fix it."
SQL does not guarantee a stable order for rows that tie on the ORDER BY keys. If two rows share the same created_at, the database is free to return them in either order on any given execution — and the order may even change between consecutive executions of the same query, depending on parallel workers, execution plan choices, and physical row layout. When you paginate with OFFSET, page 1 might see the tied pair in order (A, B) and page 2 might see them in order (B, A), which means the user sees row B on both pages (duplicate) or sees row A on neither page (skip). The fix is to append a guaranteed-unique tiebreaker to the ORDER BY, almost always the primary key: ORDER BY created_at DESC, id DESC. Now every row has a unique position in the total order and pagination is reproducible. For even better performance on large tables, switch from OFFSET pagination to keyset (also called seek) pagination: WHERE (created_at, id) < (:last_seen_created_at, :last_seen_id) ORDER BY created_at DESC, id DESC LIMIT 20. This uses the index directly, runs in O(log N) per page regardless of how deep you go, and is still deterministic because of the tiebreaker.
4. "Can ORDER BY reference aliases from the SELECT list? What about WHERE and GROUP BY?"
ORDER BY can reference aliases from the SELECT list in every major database — this is because ORDER BY is logically evaluated after SELECT, so by the time sorting happens, the alias name exists. SELECT total * 0.08 AS tax FROM orders ORDER BY tax DESC works and is cleaner than repeating the expression. WHERE cannot reference SELECT aliases in standard SQL, because WHERE is logically evaluated before SELECT — the alias does not yet exist when the row filter runs. GROUP BY also cannot reference SELECT aliases in standard SQL, though MySQL and Postgres allow it as an extension. HAVING can reference aggregates and, in some dialects, aliases. The safe portable rule: use aliases only in ORDER BY and in HAVING (for aggregates); repeat the expression in WHERE and GROUP BY, or wrap the query in a subquery or CTE that exposes the alias as a real column.
5. "What is ORDER BY column position (like ORDER BY 2) and should you use it in production code?"
ORDER BY 2 means "sort by the second column in the SELECT list." It is a shorthand supported by most databases for ad-hoc queries where retyping a long expression would be tedious — for example, SELECT customer, SUM(total) FROM orders GROUP BY customer ORDER BY 2 DESC sorts by the sum without having to repeat SUM(total) in the ORDER BY. The risk is that the position is a reference to a physical slot in the SELECT list, not to a named concept. If anyone later adds a column to the SELECT clause, every position shifts and the sort silently reinterprets to mean a different column. This is a classic source of "the report looks different after the refactor" bugs. The recommendation is: use positional ORDER BY freely at the SQL prompt, and use named columns or aliases in any query that ships in application code, views, stored procedures, or migrations.
Quick Reference — ORDER BY Cheat Sheet
+---------------------------------------------------------------+
| ORDER BY CHEAT SHEET |
+---------------------------------------------------------------+
| |
| DIRECTION: |
| ORDER BY col -> ASC (default) |
| ORDER BY col ASC -> smallest first |
| ORDER BY col DESC -> largest first |
| Mix freely: ORDER BY a ASC, b DESC |
| |
| NULLS (Postgres): |
| ASC default -> NULLS LAST |
| DESC default -> NULLS FIRST |
| Override: NULLS FIRST / NULLS LAST |
| |
| NULLS (MySQL / SQL Server): |
| ASC default -> NULLS FIRST |
| DESC default -> NULLS LAST |
| Workaround: ORDER BY col IS NULL, col |
| |
| TARGETS: |
| ORDER BY column_name |
| ORDER BY alias_from_select |
| ORDER BY expression (e.g. LOWER(name)) |
| ORDER BY 2 (column position - avoid in code) |
| |
| PAGINATION: |
| Always add a unique tiebreaker (PK) to ORDER BY |
| Prefer keyset/seek over OFFSET for deep pages |
| |
| PERFORMANCE: |
| Matching index -> free ordered walk, no Sort node |
| Backward match -> Index Scan Backward, also free |
| Mismatch -> Sort node (quicksort or external merge) |
| LIMIT + index -> O(log N) top-K, stops early |
| |
+---------------------------------------------------------------+
+---------------------------------------------------------------+
| KEY RULES |
+---------------------------------------------------------------+
| |
| 1. ASC is the default -- say DESC explicitly when needed |
| 2. Never rely on default NULL position across engines |
| 3. Multi-column ORDER BY = lexicographic tiebreaker chain |
| 4. SQL sorts are NOT guaranteed stable -- add a PK tiebreaker |
| 5. ORDER BY can use SELECT aliases; WHERE and GROUP BY cannot |
| 6. GROUP BY does not imply sorted output -- add ORDER BY |
| 7. Avoid ORDER BY column_position in production code |
| 8. Check EXPLAIN for a Sort node -- its absence means free |
| 9. Pagination without a unique tiebreaker is a bug |
| 10. For hot ORDER BY + LIMIT, create a matching index |
| |
+---------------------------------------------------------------+| Concern | Wrong Way | Right Way |
|---|---|---|
| NULL position | Trust the default | NULLS FIRST / NULLS LAST explicit |
| Tied rows | ORDER BY created_at only | ORDER BY created_at, id |
| Pagination | LIMIT 20 OFFSET 40 on timestamp | Add PK tiebreaker, prefer keyset |
| Sort by aggregate | Repeat SUM(total) | ORDER BY alias from SELECT |
| Column reference | ORDER BY 2 in app code | ORDER BY total_revenue |
| Huge sort | No index, external merge | Create index matching ORDER BY |
| GROUP BY output | Assume it's sorted | Explicit ORDER BY after |
| Mixed ASC/DESC | Plain index on both cols | Index with matching directions |
Prev: Lesson 4.5 -- HAVING vs WHERE Next: Lesson 5.1 -- What are Joins
This is Lesson 4.6 of the Database Interview Prep Course -- 12 chapters, 58 lessons.