SQL: Limiting the Number of Rows Returned

Several keywords control how many rows are returned in a query's result set. The specific keyword depends on the database system you are using.

Limiting Rows: SELECT TOP, LIMIT, and ROWNUM

These keywords provide ways to retrieve a subset of rows rather than the entire result set of a query. This is very useful for getting quick previews, optimizing query performance, or handling paginated results.

Database-Specific Keywords

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

Syntax

The general approach is similar across systems, although the specific keyword differs:

-- 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 to retrieve.

Examples

SQL Server: SELECT TOP

Retrieving the top three rows from the 'Customers' table (SQL Server). (Assumes a 'Customers' table exists.)

SELECT TOP 3 * FROM Customers;
      

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

MySQL: LIMIT

The equivalent query using LIMIT in MySQL.

SELECT * FROM Customers LIMIT 3;
      

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

Oracle: ROWNUM

The equivalent query using ROWNUM in Oracle.

SELECT * FROM Customers WHERE ROWNUM <= 3;
      

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