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.

Example

Select only the first 3 records of the employee table:
 SELECT TOP  3
* FROM  employee ;

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 Employee table

This employee table is used for examples:

LIMIT

The following SQL statement shows the equivalent example for MySQL:

Example

Select only the first 3 records of the employee table:

 SELECT * FROM employee
 LIMIT   3  ;

FETCH FIRST

The following SQL statement shows the equivalent example for Oracle:

Example

Select only the first 3 records of the employee table:
 SELECT * FROM employee
 FETCH  FIRST  3  ROWS ONLY;

SQL TOP PERCENT Example

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

Example

 SELECT TOP  50  PERCENT *
 FROM  employee;

The following SQL statement shows the equivalent example for Oracle:

Example

 SELECT  *  FROM  employee 
 FETCH  FIRST  50  PERCENT  ROWS ONLY;

ADD a WHERE CLAUSE

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

Example

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

The following SQL statement shows the equivalent example for MySQL:

Example

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

You can click on above box to edit the code and run again.

Output

The following SQL statement shows the equivalent example for Oracle:

Example

 SELECT  *  FROM  employee 
 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:

Example

Sort the result reverse alphabetically by Employee_name, and return the first 3 records:
 SELECT TOP  3
* FROM  employee 
 ORDER BY Employee_name  DESC  ;

The following SQL statement shows the equivalent example for MySQL:

Example

  SELECT  
* FROM  employee
 ORDER BY  Employee_name  DESC  
 LIMIT   3  ;

You can click on above box to edit the code and run again.

Output

The following SQL statement shows the equivalent example for Oracle:

Example

 SELECT  *  FROM  employee 
 ORDER BY  Employee_name  DESC  
 FETCH  FIRST  3  ROWS ONLY;

You can click on above box to edit the code and run again.