SQL TOP/LIMIT/ROWNUM

In this tutorial, we will learn how to retrieve a fixed number of records from a table of the database using SQL. 

SQL TOP, LIMIT or ROWNUM Clause

SQL SELECT TOP clause is used to fetch the limited number of records from the table.

SELECT TOP clause supports MS SQL Server/MS Acces.

Syntax of SELECT TOP Clause 

SELECT TOP [Number | Percent] columns
FROM tableName 
WHERE condition;

Syntax of LIMIT Clause

LIMIT clause supports in MySQL.

SELECT columns
FROM tableName
WHERE condition
LIMIT number;

Syntax of ROWNUM Clause

ROWNUM clause supports in Oracle

SELECT columns
FROM tableName
WHERE ROWNUM <= number;

Example:

Let us consider this table "Employee" for records.

Table Name: Employee

Example of SELECT TOP clause

If you use SQL Server, then for fetch top 3 records, a query will be: 

SELECT TOP 3 FROM Employee;

Example of LIMIT clause

If you use MySQL Server, then for fetch top 3 records, a query will be:

SELECT * 
FROM Employee
LIMIT 3;

Example of ROWNUM Clause

If you use Oracle, then for fetch top 3 records, a query will be:

SELECT * 
FROM Employee
WHERE ROWNUM <=3;

TOP, LIMIT or ROWNUM Clause using with WHERE clause in the Statement

If you use SQL Server, then for fetch top 3 records WHERE Country="India", a query will be:

SELECT TOP 3 
FROM Employee 
WHERE Country='India';

If you use MySQL Server, then for fetch top 3 records WHERE Country="India", a query will be:

SELECT * 
FROM Employee
WHERE Country='India'
LIMIT 3;

If you use Oracle, then for fetch top 3 records WHERE Country="India", a query will be:

SELECT * 
FROM Employee
WHERE Country='India' AND ROWNUM <=3;