Facebook Page Twitter Page LinkedIn Page
× SQL Tutorial SQL Advanced SQL Database SQL Resources


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 tables called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

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

    /* SQL Server / MS Access Query */
    SELECT TOP 3 * FROM Employee;
    
    /* MySQL Query */
    SELECT * FROM Employee LIMIT 3;
    
    /* Oracle Query */
    SELECT * FROM Employee WHERE ROWNUM <= 3;
    

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    2MarryNew York242750
    3JoParis272800
  • In SQL Server and MS Access, the above result can also be achieved by using PERCENT keyword in the query.

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

    This result of the following code will be:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    2MarryNew York242750
    3JoParis272800

5