Limiting Query Results: TOP, LIMIT, and ROWNUM

Database systems offer various ways to limit the number of rows returned by a query. This is helpful for performance, displaying paginated results, or getting a quick preview of your data. The specific syntax differs across database systems.

Limiting the Number of Rows

Here's how to limit the number of rows returned, depending on your database system:

SQL Server: Uses SELECT TOP
MySQL: Uses LIMIT
Oracle: Uses ROWNUM

Syntax


-- SQL Server:
SELECT TOP number * FROM table_name;

-- MySQL:
SELECT * FROM table_name LIMIT number;

-- Oracle:
SELECT * FROM table_name WHERE ROWNUM <= number;

Replace number with the desired number of rows. Note that for Oracle, this simple approach returns the first 'number' rows only without any specific order. Using `ORDER BY` within a subquery is necessary to guarantee a consistent and ordered subset of rows.

Examples

SQL Server: SELECT TOP

This gets the top 3 rows from the 'Customers' table in SQL Server. (Assumes a table named 'Customers' exists.)

Syntax


SELECT TOP 3 * FROM Customers;

Output


(The first three rows of the Customers table will be returned.)

MySQL: LIMIT

This does the same in MySQL using LIMIT.

Syntax


SELECT * FROM Customers LIMIT 3;

Output


(The first three rows of the Customers table will be returned.)

Oracle: ROWNUM

This Oracle example uses ROWNUM. Note that it returns the first three rows only, without any specific ordering.

Syntax


SELECT * FROM Customers WHERE ROWNUM <= 3;

Output


(The first three rows of the Customers table will be returned.)