SQL - TOP, LIMIT or ROWNUM Clause

The SQL TOP Clause is used to fetch specified number or percentage of records from a table. This is useful when the table contains thousands of records and returning a large dataset can impact performance.

Note: TOP clause is not supported in all database. For example MySQL supports the LIMIT clause and Oracle uses ROWNUM keyword to fetch limited number of records.

Syntax

The syntax for using TOP Clause is given below:

/* SQL Server / MS Access Syntax */
SELECT TOP number|percent column1, column2, ...
FROM table_name
WHERE condition(s);

/* MySQL Syntax */
SELECT column1, column2, ...
FROM table_name
WHERE condition(s)
LIMIT number;

/* Oracle Syntax */
SELECT column1, column2, ...
FROM table_name
WHERE condition(s) AND ROWNUM <= number;

Example:

Consider a database containing a table called Employee with the following records:

• To fetch top 3 records from the Employee table, the SQL query is:

  /* SQL Server / MS Access Query */
  SELECT TOP 3 * FROM Employee;
  
  OR
  
  /* SQL Server / MS Access Query */
  SELECT TOP 50 PERCENT * FROM Employee;
  

• In Oracle, the above result can be achieved using ROWNUM keyword. Consider the example below:

  /* Oracle Query */
  SELECT * FROM Employee WHERE ROWNUM <= 3;
  

• In MySQL, the above result can be achieved using LIMIT keyword. Consider the example below:

  /* MySQL Query */
  SELECT * FROM Employee LIMIT 3;
  

  
  

  This will produce the result as shown below:

  EmpID	Name	City	Age	Salary
  1	John	London	25	3000
  2	Marry	New York	24	2750
  3	Jo	Paris	27	2800