T-SQL reference › TOP

SELECT TOP in SQL Server, and why it needs ORDER BY

TOP limits how many rows come back. SQL Server’s version of LIMIT. On its own it doesn’t pick the biggest, the newest or anything else.

The five biggest orders

SELECT TOP (5) order_id, customer_id, total_amount
FROM Orders
ORDER BY total_amount DESC;

TOP 5 and TOP (5) both work. The brackets are required when the number is a variable or an expression, so many people always use them.

TOP without ORDER BY

SELECT TOP 5 * FROM Orders returns five rows, but which five is up to SQL Server. It might look sorted today and not next week. If the question says biggest, newest or first, you need ORDER BY.

Ties

SELECT TOP (3) WITH TIES customer_id, total_spent
FROM CustomerTotals
ORDER BY total_spent DESC;

WITH TIES keeps any extra rows that tie with the last one, so you might get more than three.

A percentage

SELECT TOP (10) PERCENT order_id, total_amount FROM Orders ORDER BY total_amount DESC;

Coming from MySQL or PostgreSQL?

SQL Server doesn’t have LIMIT. Use TOP, or OFFSET … FETCH NEXT … ROWS ONLY after an ORDER BY for paging.

Try it on Harbour & Co

Sandra Okafor (Managing Director) needs this now: “Board pack: our 5 biggest orders ever. Order, customer, amount.” Write the query, run it, send the results. On the paid plan.

Take the ticket →