Limiting Query Results: TOP, LIMIT, and ROWNUM

Often, you only need a subset of the data from a query, rather than the entire result set. This is especially important for performance when working with large tables. Database systems use different keywords to limit the number of rows returned.

Limiting Rows Returned

Here's how to limit the number of rows returned in SQL, 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 number of rows you want to retrieve. Note that in Oracle, this will only give you the first 'number' rows. To get a specific number of rows after applying an `ORDER BY` clause, a subquery is necessary.

Examples

Limiting Rows in SQL Server

This SQL Server example selects the top 3 rows from the 'Customers' table (assuming a 'Customers' table exists).

SELECT TOP 3 * FROM Customers;
      

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

Limiting Rows in MySQL

This MySQL example achieves the same using the LIMIT clause.

SELECT * FROM Customers LIMIT 3;
      

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

Limiting Rows in Oracle

This Oracle example uses ROWNUM to achieve the same result. Note that this will only retrieve the first three rows; to use this with `ORDER BY` you would need to use a subquery.

SELECT * FROM Customers WHERE ROWNUM <= 3;
      

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