SQL Select Top / Limit / Rownum

Because different database systems (like SQL Server, MySQL, and Oracle) were built independently, they use different syntaxes to accomplish the exact same task: restricting the number of rows returned by a query.

Capping your results is essential when handling large tables to prevent queries from draining database memory.

The Three Variations Compared

Syntax Examples

MySQL / PostgreSQL / SQLite (LIMIT)

The LIMIT clause goes at the absolute end of your statement.

SELECT product_name, price FROM products
ORDER BY price DESCLIMIT 5;

SQL Server / MS Access (SELECT TOP)

The TOP clause is placed immediately after SELECT. You can use a flat number or a percentage.

-- Returns the top 5 rows
SELECT TOP 5 product_name, price
FROM products
ORDER BY price DESC;

-- Returns the top 10% of all rows in the dataset
SELECT TOP 10 PERCENT product_name, price
FROM products
ORDER BY price DESC;

Oracle Database (ROWNUM)

In older Oracle versions, you filter by the virtual column ROWNUM inside your WHERE clause.

SELECT * FROM products
WHERE ROWNUM <= 5;

Note: In modern Oracle (12c and newer), you can also use the standard ANSI syntax at the end of a query: FETCH FIRST 5 ROWS ONLY;.

Critical Rule: Always Pair with ORDER BY

If you use LIMIT, TOP, or ROWNUM without an ORDER BY clause, the database will hand back a random selection of rows based on whatever it finds first in memory. To reliably get a specific set (like the top 5 most expensive items or the 10 oldest users), you must tell the database how to sort the data first.

Advanced: Skipping Rows (OFFSET)

If you are building pagination for an app (e.g., displaying results 11 to 20 on "Page 2"), you can use OFFSET alongside LIMIT to skip a specified number of rows.

-- Skips the first 10 rows and returns the next 10 (Rows 11-20)
SELECT customer_name 
FROM customers
ORDER BY customer_name ASC
LIMIT 10 OFFSET 10;