The page explains how to limit the number of records returned from a SQL query using the SELECT TOP, LIMIT, FETCH FIRST, or ROWNUM clauses, depending on the database system:

• SQL Server/MS Access: Use SELECT TOP to specify the number or percentage of rows (e.g., SELECT TOP 3 * FROM Customers;).
• MySQL: Use the LIMIT clause at the end (e.g., SELECT * FROM Customers LIMIT 3;).
• Oracle (12c+): Use FETCH FIRST n ROWS ONLY (e.g., SELECT * FROM Customers FETCH FIRST 3 ROWS ONLY;).
• Older Oracle: Use ROWNUM for row limits.

The page provides:

• Syntax examples for each database.
• Examples of selecting top N rows, top PERCENT rows, adding WHERE and ORDER BY clauses, and combining limits with sorting.
• An interactive quiz and reference links for further learning.

The explanation highlights that limiting query results improves performance, especially for large tables, and syntax differs among major SQL systems.[1]

1

SQL TOP, LIMIT, FETCH FIRST or ROWNUM Clause

The SQL SELECT TOP Clause

The SELECT TOP clause is used to specify the number of records to return.

The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.

Select only the first 3 records of the Customers table:

SELECT TOP 3 * FROM Customers;

Note: Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses FETCH FIRST _n_ ROWS ONLY and ROWNUM.

SQL Server / MS Access Syntax:

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

MySQL Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

Oracle 12 Syntax:

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s)
FETCH FIRST number ROWS ONLY;

Older Oracle Syntax:

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

Older Oracle Syntax (with ORDER BY):

SELECT *
FROM (SELECT column_name(s) FROM table_name ORDER BY column_name(s))
WHERE ROWNUM <= number;

Demo Database

Below is a selection from the Customers table used in the examples:

LIMIT

The following SQL statement shows the equivalent example for MySQL:

Select the first 3 records of the Customers table:

SELECT * FROM Customers  
LIMIT 3;

FETCH FIRST

The following SQL statement shows the equivalent example for Oracle:

Select the first 3 records of the Customers table:

SELECT * FROM Customers  
FETCH FIRST 3 ROWS ONLY;

SQL TOP PERCENT Example

The following SQL statement selects the first 50% of the records from the "Customers" table (for SQL Server/MS Access):

SELECT TOP 50 PERCENT * FROM Customers;

The following SQL statement shows the equivalent example for Oracle:

SELECT * FROM Customers
FETCH FIRST 50 PERCENT ROWS ONLY;

ADD a WHERE CLAUSE

The following SQL statement selects the first three records from the "Customers" table, where the country is "Germany" (for SQL Server/MS Access):

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

The following SQL statement shows the equivalent example for MySQL:

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

The following SQL statement shows the equivalent example for Oracle:

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

ADD the ORDER BY Keyword

Add the ORDER BY keyword when you want to sort the result, and return the first 3 records of the sorted result.

For SQL Server and MS Access:

Sort the result reverse alphabetically by CustomerName, and return the first 3 records:

SELECT TOP 3 * FROM Customers
ORDER BY CustomerName DESC;

The following SQL statement shows the equivalent example for MySQL:

SELECT * FROM Customers
ORDER BY CustomerName DESC
LIMIT 3;

The following SQL statement shows the equivalent example for Oracle:

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