Limiting Query Results: TOP, LIMIT, and ROWNUM

Database systems offer different ways to limit the number of rows returned by a query. This is helpful for performance, for displaying paginated results, or for previewing data. The specific keyword depends on which database system you're using.

Limiting Rows Returned

Different database systems use different keywords to limit the number of rows returned:

• SQL Server: SELECT TOP
• MySQL: LIMIT
• Oracle: 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. Note that in Oracle, this simple method only gets the first few rows; using `ORDER BY` within a subquery is necessary to get a specific, ordered subset.

Examples

SQL Server: SELECT TOP

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 of the Customers table will be returned.)
      

MySQL: LIMIT

This MySQL example uses LIMIT to achieve the same result.

SELECT * FROM Customers LIMIT 3;
      

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

Oracle: ROWNUM

This Oracle example uses ROWNUM. Note that this will only retrieve the first three rows, and the order is not guaranteed without an `ORDER BY` clause.

SELECT * FROM Customers WHERE ROWNUM <= 3;
      

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