Limiting Query Results: TOP, LIMIT, FETCH FIRST, and ROWNUM

In SQL, you often want to retrieve only a subset of the data, rather than the entire result set. This is useful for performance reasons, for displaying paginated results, or for getting a quick preview of your data. The way you limit the number of rows depends on your database system.

Limiting Rows Returned

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

• SQL Server and MS Access: SELECT TOP
• MySQL: LIMIT
• Oracle: FETCH FIRST (Oracle 12 and later) or ROWNUM (older Oracle versions)

General Syntax

The basic idea is the same across systems, but the exact keyword varies:

-- SQL Server and MS Access:
SELECT TOP number|percent * FROM table_name WHERE condition;

-- MySQL:
SELECT * FROM table_name WHERE condition LIMIT number;

-- Oracle 12 and later:
SELECT * FROM table_name WHERE condition ORDER BY column_name FETCH FIRST number ROWS ONLY;

-- Older Oracle (without ORDER BY):
SELECT * FROM table_name WHERE ROWNUM <= number;

--Older Oracle (with ORDER BY):
SELECT * FROM (SELECT * FROM table_name ORDER BY column_name) WHERE ROWNUM <= number;
      

Replace number with the number of rows you want and percent with the percentage of rows (e.g., 50 PERCENT).

Examples

Selecting Top N Rows (SQL Server/MS Access)

This SQL Server and MS Access example selects the top 3 rows from the Customers table. (Assumes a 'Customers' table exists)

SELECT TOP 3 * FROM Customers;
      

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

Selecting Top N Rows (MySQL)

This MySQL example does the same using the LIMIT clause.

SELECT * FROM Customers LIMIT 3;
      

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

Selecting Top N Rows (Oracle 12c and later)

Oracle 12c and later versions use FETCH FIRST. Note that `ORDER BY` is required.

SELECT * FROM Customers
ORDER BY CustomerID
FETCH FIRST 3 ROWS ONLY;
      

(The first three rows of the Customers table, ordered by CustomerID, will be returned.)
      

Selecting Top N Rows (Older Oracle)

Older versions of Oracle use ROWNUM. Note that `ORDER BY` needs to be used within a subquery for correct results.

SELECT * FROM (SELECT * FROM Customers ORDER BY CustomerID) WHERE ROWNUM <= 3;
      

(The first three rows of the Customers table, ordered by CustomerID, will be returned.)
      

SELECT TOP 50 PERCENT * FROM Customers;
      

(The top 50% of rows from the Customers table will be returned.)
      

SELECT * FROM Customers
FETCH FIRST 50 PERCENT ROWS ONLY;
      

(The top 50% of rows from the Customers table will be returned.)
      

SELECT TOP 3 * FROM Customers WHERE Country = 'Germany';
      

(The first three rows from the Customers table where Country is 'Germany' will be returned.)
      

SELECT * FROM Customers WHERE Country = 'Germany' LIMIT 3;
      

(The first three rows from the Customers table where Country is 'Germany' will be returned.)
      

SELECT * FROM Customers WHERE Country = 'Germany' FETCH FIRST 3 ROWS ONLY;
      

(The first three rows from the Customers table where Country is 'Germany' will be returned.)
      

SELECT TOP 3 * FROM Customers ORDER BY CustomerName DESC;
      

(The top 3 rows, sorted descending by CustomerName, will be returned.)
      

SELECT * FROM Customers ORDER BY CustomerName DESC LIMIT 3;
      

(The top 3 rows, sorted descending by CustomerName, will be returned.)
      

SELECT * FROM Customers ORDER BY CustomerName DESC FETCH FIRST 3 ROWS ONLY;
      

(The top 3 rows, sorted descending by CustomerName, will be returned.)