TOP vs LIMIT Across Dialects

There is no single standard spelling, which is why this comes up in almost every SQL interview. SQL Server has its own TOP keyword, MySQL and SQLite and PostgreSQL use LIMIT, SQL Server's modern syntax uses OFFSET/FETCH, and Oracle 12c and later use FETCH FIRST. Only LIMIT and OFFSET are close to the standard.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL
);

INSERT INTO products (id, name, unit_price) VALUES
  (1, 'Mechanical Keyboard', 79.50),
  (2, 'USB-C Hub', 32.00),
  (3, '27-inch Monitor', 249.99),
  (4, 'Standing Desk', 419.00);

-- SQLite, PostgreSQL, MySQL:
SELECT name, unit_price FROM products ORDER BY unit_price DESC LIMIT 3;

-- SQL Server (legacy TOP):
-- SELECT TOP (3) name, unit_price FROM products ORDER BY unit_price DESC;

-- SQL Server (modern OFFSET/FETCH, ORDER BY is mandatory):
-- SELECT name, unit_price FROM products ORDER BY unit_price DESC
--   OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

-- Oracle 12c and later:
-- SELECT name, unit_price FROM products ORDER BY unit_price DESC FETCH FIRST 3 ROWS ONLY;

The runnable statement returns three rows: the Standing Desk at 419.00, the 27-inch Monitor at 249.99 and the Mechanical Keyboard at 79.50. The commented lines are the same request in the other dialects.

Two Oracle-specific cautions. Before 12c, Oracle had no row-limiting clause at all — you had to wrap the query in a subquery and filter on ROWNUM, and ROWNUM is assigned before the ORDER BY runs, so the classic WHERE ROWNUM <= 3 pattern returns three arbitrary rows rather than the top three after sorting. That bug is still taught in interviews. And Oracle's FETCH FIRST requires the ORDER BY to come before it, unlike the LIMIT clause in SQLite where the order of the keywords is LIMIT then OFFSET.

One more difference worth flagging: SQL Server's TOP accepts a variable or a parenthesised expression and can be combined with PERCENT, which LIMIT cannot do. If a report needs the top 10 percent of customers, that is a genuinely different piece of work on each engine.

Aggregate first, then sort by the aggregate, then take the top rows. The alias from the aggregate can be used in ORDER BY, so there is no need to repeat the SUM expression.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper'),
  (3, 'Alan Turing'),
  (4, 'Joan Clarke'),
  (5, 'Katherine Johnson'),
  (6, 'Claude Shannon');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00), (2, 1, 340.50), (3, 1,  90.00),
  (4, 2, 940.00),
  (5, 3, 610.00), (6, 3, 205.00),
  (7, 4,  75.25),
  (8, 5, 480.00), (9, 5, 120.00);

SELECT c.name,
       ROUND(SUM(o.amount), 2) AS total_spent,
       COUNT(o.id) AS order_count
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY total_spent DESC
LIMIT 5;

Five rows come back in descending order of spending: Grace Hopper at 940.00, Alan Turing at 815.00, Katherine Johnson at 600.00, Ada Lovelace at 550.50 and Joan Clarke at 75.25. Claude Shannon is absent because she has no orders at all, and the inner join drops her before the limit is ever applied.

Two things a reviewer should notice. The query uses an inner JOIN, so customers with no orders do not appear; if the report should show them with a zero, switch to a LEFT JOIN and COALESCE the sum. And the tiebreaker matters: if two customers spent the same amount, the order between them is unspecified, so add c.id as a final sort key to make the page deterministic.

One more subtlety: LIMIT applied after the aggregation gives you the top five customers. LIMIT applied inside a subquery before the aggregation gives you something entirely different, and it is a classic mistake in interviews — SELECT customer_id FROM orders LIMIT 5 takes the first five order rows, not the five customers who spent the most.

Whether the limit is cheap depends on whether the engine can find the top rows in the index without sorting the whole table first. That requires the index to already be in the same order as the ORDER BY. If it is, the engine walks the index in order and stops after the limit — it reads exactly as many rows as it returns. If it is not, the engine must collect and sort every matching row before it can discard all but the first ten, and the limit saves you nothing.

The syntax itself matters less than people expect. LIMIT, TOP and FETCH FIRST all express the same intent, and all three can use an index when the ordering matches — TOP is just a different spelling of the same limit node in the execution plan. The real determinants are the ORDER BY clause and the index definition.

So SELECT ... FROM orders ORDER BY created_at DESC LIMIT 10 is fast if there is a composite index on (created_at DESC), or on (created_at, id) if you add a tiebreaker. Without that index, the engine scans and sorts the whole table to return ten rows out of a million.

Three details complicate it. First, a covering index changes the picture: if the index contains every column you project, the engine can answer the query from the index alone and never touch the table. Second, a WHERE clause that filters on a different column means the engine must find matching rows and then sort them, which usually forces a full sort unless the planner can use an index intersection. Third, the ORDER BY must be a prefix-compatible match of the index: an index on (customer_id, created_at) cannot serve ORDER BY created_at for the whole table, only for a single customer.

The practical diagnostic is to look at the plan. If you see a full table scan followed by a sort, the limit is not saving you anything and an index on the sort key is the fix. If you see an index scan that stops early, the limit is doing its job.