TOP: Limit the Rows

"Show the top 3 run-scorers." You don't need all 6 — or all 6 million — rows, just the first few after sorting. SQL Server's TOP keyword cuts the result down to a fixed number of rows.

Definition

TOP n limits the result to the first n rows. It is applied last, after the result has been sorted by ORDER BY. Without ORDER BY, which n rows you get is not defined — any n rows may come back.

Syntax

SELECT TOP n column_list
FROM table_name
ORDER BY column DESC;

-- variations (SQL Server)
SELECT TOP n PERCENT column_list ...      -- first n% of rows
SELECT TOP n WITH TIES column_list ...    -- include rows tied with the last one (needs ORDER BY)

Example — top 3 run-scorers

SELECT TOP 3 *
FROM players
ORDER BY runs DESC;

Execution order: 1️⃣ FROM → 2️⃣ SELECT * → 3️⃣ ORDER BY runs DESC → 4️⃣ TOP 3. Even though TOP is written first, it is applied last — after sorting.

Example — top 3 individual innings

SELECT TOP 3 player, match_date, runs
FROM innings
ORDER BY runs DESC;

WITH TIES — fair cut-offs

In innings, Kohli and Buttler both scored 73. TOP 4 would show only one of them (whichever the engine reaches first). TOP 4 WITH TIES returns 5 rows — 107, 82, 76, 73, 73 — because both 73s tie for 4th place:

SELECT TOP 4 WITH TIES player, runs
FROM innings
ORDER BY runs DESC;

(WITH TIES and PERCENT are SQL Server features and don't run in the browser playground.)

SQL Server vs MySQL / PostgreSQL / Oracle

Note the position: TOP goes right after SELECT; LIMIT goes at the very end. SQL Server also supports the standard OFFSET … FETCH for paging (Chapter 9).

Common mistakes

• ❌ SELECT TOP 3 * FROM players without ORDER BY and calling it "top 3 scorers". ✅ Always pair TOP with ORDER BY when "top" means "best".
• ❌ Writing LIMIT 3 in SQL Server → syntax error. ✅ Use TOP 3.
• ❌ SELECT name, TOP 3 runs → syntax error. ✅ TOP must come immediately after SELECT (or after SELECT DISTINCT).
• ❌ Expecting ORDER BY runs ASC + TOP to give the best scorers. ✅ ASC gives the lowest first — use DESC for the highest.

Interview tip

Q: How do you get the 2nd highest run-scorer? A: One simple way: take the top 2 by runs and then pick the lower one — SELECT TOP 1 * FROM (SELECT TOP 2 * FROM players ORDER BY runs DESC) AS t ORDER BY runs ASC; — returns Virat Kohli (750). Later chapters show cleaner ways with OFFSET … FETCH and DENSE_RANK().