SQL / SORTING, LIMITING, AND BRANCHING OUTPUT
Limiting rows when every engine spells it differently
Write a first-n-rows query as LIMIT, TOP or FETCH FIRST, know why all three run last, and dodge Oracle's ROWNUM-before-sort trap.
What you will learn
- Translate one query between LIMIT n, TOP (n) and FETCH FIRST n ROWS ONLY
- Put the row limit last in the pipeline: after GROUP BY, DISTINCT and ORDER BY
- Choose between ONLY and WITH TIES when the cut-off falls inside a tied group
- Rewrite a limit as ROW_NUMBER() in a subquery when one query must run everywhere
Understanding Limiting rows when every engine spells it differently
Every engine can answer "give me the highest three scores", but the clause that does the cutting was invented independently several times before the standard caught up. MySQL, PostgreSQL and SQLite inherited LIMIT n from the same 1990s lineage and put it at the end of the statement; Microsoft put TOP (n) immediately after SELECT in T-SQL; Oracle before 12c had no such clause at all and made you compare against the ROWNUM pseudo-column; SQL:2008 finally standardised FETCH FIRST n ROWS ONLY, which Db2, Oracle 12c and later, PostgreSQL and MariaDB 10.6+ all accept. Learning this topic means learning one idea and four spellings, not four features.
The idea is that row limiting is the last step of query evaluation. Rows are read, filtered by WHERE, grouped, filtered again by HAVING, projected by the select list, de-duplicated by DISTINCT, sorted by ORDER BY, and only then does the engine count off n rows and stop. That is why T-SQL's TOP (n), written before the select list, still acts after the sort, and why a limit with no ORDER BY is a legitimate request for any n rows: the engine returns whichever rows its plan produced first, and that set can change when an index appears or the table grows.
Oracle's ROWNUM is the one construct that does not fit the model, because it is assigned as the query block emits rows, before ORDER BY runs. So WHERE ROWNUM <= 3 combined with ORDER BY points DESC grabs three arbitrary rows and sorts those three; the fix is to sort inside an inline view and compare ROWNUM in the query outside it. The dialects also disagree about extras: SQL Server's TOP accepts PERCENT and WITH TIES, standard FETCH FIRST accepts WITH TIES (PostgreSQL 13 and later), and MySQL and SQLite offer neither, so ROW_NUMBER() or RANK() in a derived table is the one form that behaves the same everywhere.
Once you see the limit as the final step, porting a query becomes mechanical: keep the ORDER BY intact, move the count to wherever that dialect expects it, and only then worry about ties and percentages.
-- PostgreSQL: one question, two spellings, identical results.
WITH score(player, points) AS (
VALUES ('ada', 91), ('bo', 91), ('cy', 88), ('dee', 74), ('eli', 60)
)
SELECT player, points
FROM score
ORDER BY points DESC, player
LIMIT 3; -- MySQL, PostgreSQL, SQLite, MariaDB
WITH score(player, points) AS (
VALUES ('ada', 91), ('bo', 91), ('cy', 88), ('dee', 74), ('eli', 60)
)
SELECT player, points
FROM score
ORDER BY points DESC, player
FETCH FIRST 3 ROWS ONLY; -- Db2, Oracle 12c+, PostgreSQL, MariaDB 10.6+
-- SQL Server: SELECT TOP (3) player, points FROM score ORDER BY points DESC, player;Every dialect's row-limiting clause names the same final step, keeping the first n rows of the finished sorted result, with Oracle's pre-sort ROWNUM as the one exception.
Worked examples
ONLY versus WITH TIES
Shows how WITH TIES can return more rows than the number you asked for.
WITH score(player, points) AS (
VALUES ('ada', 91), ('bo', 91), ('cy', 88)
)
SELECT points
FROM score
ORDER BY points DESC
FETCH FIRST 1 ROW WITH TIES;Example explained
Line 1FETCH FIRST 1 ROW WITH TIES keeps the first row plus every row whose ORDER BY key equals it, so 1 becomes 2 here.
Line 2WITH TIES is only legal alongside an ORDER BY, because without a sort key there is no definition of 'tied'.
Line 3Swap it for ONLY, or for LIMIT 1, and you get a single 91 with the engine picking one of the two tied rows arbitrarily.
Line 4SQL Server spells the same feature SELECT TOP (1) WITH TIES; MySQL and SQLite have no equivalent.
The portable fallback: ROW_NUMBER()
Expresses a first-n-rows limit in a form that parses on every current major engine.
WITH score(player, points) AS (
VALUES ('ada', 91), ('bo', 91), ('cy', 88), ('dee', 74)
), ranked AS (
SELECT player,
points,
ROW_NUMBER() OVER (ORDER BY points DESC, player) AS rn
FROM score
)
SELECT player, points
FROM ranked
WHERE rn <= 2
ORDER BY rn;Example explained
Line 1ROW_NUMBER() OVER (ORDER BY points DESC, player) numbers the rows 1..n in the order you care about, so rn <= 2 means 'the first two'.
Line 2The filter must sit in an outer query: window functions are evaluated after WHERE, so WHERE ROW_NUMBER() OVER (...) <= 2 is rejected.
Line 3The tie-break on player makes the numbering repeatable; with points alone, ada and bo could receive 1 and 2 in either order.
Line 4This shape runs on PostgreSQL, SQL Server 2005+, Oracle, MySQL 8, MariaDB 10.2+ and SQLite 3.25+, which is why it survives a dialect change.
The limit counts output rows, not table rows
Demonstrates that the cut happens after DISTINCT and ORDER BY have already reshaped the result.
WITH score(player, points) AS (
VALUES ('ada', 91), ('bo', 91), ('cy', 88), ('dee', 74)
)
SELECT DISTINCT points
FROM score
ORDER BY points DESC
LIMIT 2;Example explained
Line 1DISTINCT collapses the four source rows to three values before the limit sees anything.
Line 2LIMIT 2 then counts result rows, so you get the two largest distinct scores rather than the first two rows of the input.
Line 3This step order is also why T-SQL's TOP (2), although written before the select list, still applies after DISTINCT and ORDER BY.
Important notes
Version matters more than vendor name: MariaDB 10.6+ understands FETCH FIRST but MySQL 8 does not, and SQLite has only ever had LIMIT.
WITH TIES can return more rows than requested, so never use it where the row count must be fixed, such as a page of exactly 20 results.
Common mistakes
Limiting without a deterministic ORDER BY and calling the result 'the top 3': the rows returned depend on the plan, so the same query yields different rows once an index is added or the table grows.
Comparing ROWNUM in the same query block as ORDER BY on Oracle: it looks correct on a small table, then silently returns the wrong rows because ROWNUM was assigned before the sort.
Expecting TOP (3) to reduce what an aggregate reads: SELECT TOP (3) SUM(points) FROM score still sums every row and returns one row, since the limit applies to the finished result set.
Try it yourself
Change, predict, then run
In a PostgreSQL browser editor, write a five-row VALUES list of products where the second- and third-highest prices are equal, then ask for the two most expensive three ways: LIMIT 2, FETCH FIRST 2 ROWS ONLY, and FETCH FIRST 2 ROWS WITH TIES. Note which one returns three rows and why.
Open the SQL workspaceCheck your understanding
A working SQL Server query, SELECT TOP (2) player, points FROM score ORDER BY points DESC, is ported to Oracle 11g as SELECT player, points FROM score WHERE ROWNUM <= 2 ORDER BY points DESC. What does the Oracle version actually do?
- Returns the two highest scores, because ORDER BY is applied before ROWNUM is assigned
- Raises an error, because ROWNUM cannot appear in a query block that has an ORDER BY
- Returns two rows chosen before the sort, so they come out sorted but are not necessarily the highest
- Returns every row, because ROWNUM restarts at 1 for each row the sort emits
Show answer
ROWNUM is assigned as the query block produces rows, so the filter keeps the first two rows the access path happens to deliver and ORDER BY only sorts those two. Option 0 is tempting because that is exactly how TOP and FETCH FIRST behave, but they are clauses applied to the finished sorted result, whereas ROWNUM is a pseudo-column evaluated during row production; the fix is to sort in an inline view and compare ROWNUM outside it.