SQL TOP Clause

Top clause is used to retrieve TOP records from database tables. You may pass how many top records you would like to retrieve from table. When you have large number of data into database table, this will help you to optimize sql statement to retrieve result set easily.

Different database has different syntax for TOP clause. For example, My Sql use LIMIT, Oracle use ROWNUM, MS Access and Sql Server use TOP.

Let’s understand each with syntax and examples.

My SQL

Syntax:

SELECT COLUMN1, COLUMN2,COLUMN3, ...
FROM TABLE_NAME
WHERE Condition
LIMIT Number;


Example:

Original Data in table called “Employee”

Id Employee Code First Name Last name Gender Birth Date
1 EMP_1001 John Peter Male 10-11-1982
2 EMP_1002 Josh Wills Male 09-07-1980
3 EMP_1003 Jeremy Parkar Female 08-09-1990
4 EMP_1004 Aidan Walls Male 05-03-1989
5 EMP_1005 Adam Mondry Male 02-06-1991


SELECT * FROM Employee
WHERE Gender = 'Male'
LIMIT3;

Data displayed from table “Employee” after above Sql Query executed


Id Employee Code First Name Last name Gender Birth Date
1 EMP_1001 John Peter Male 10-11-1982
2 EMP_1002 Josh Wills Male 09-07-1980
4 EMP_1004 Aidan Walls Male 05-03-1989

Note: Where clause is optional here

MS Sql Server

Syntax:

SELECT TOP NUMBER Column1, Columns2, Column3, ...
FROM TABLE_NAME
WHERE Condition;

Example:

Original Data in table called “Employee”


Id Employee Code First Name Last name Gender Birth Date
1 EMP_1001 John Peter Male 10-11-1982
2 EMP_1002 Josh Wills Male 09-07-1980
3 EMP_1003 Jeremy Parkar Female 08-09-1990
4 EMP_1004 Aidan Walls Male 05-03-1989
5 EMP_1005 Adam Mondry Male 02-06-1991


SELECT TOP 3 * FROM Employee
WHERE Gender = 'Male'

Data displayed from table “Employee” after above Sql Query executed


Id Employee Code First Name Last name Gender Birth Date
1 EMP_1001 John Peter Male 10-11-1982
2 EMP_1002 Josh Wills Male 09-07-1980
4 EMP_1004 Aidan Walls Male 05-03-1989

Note: Where clause is optional here

Oracle

Syntax:

SELECT Column1, Columns2, Column3, ...
FROM TABLE_NAME
WHERE ROWNUM <= Number;

Example:

Original Data in table called “Employee”


Id Employee Code First Name Last name Gender Birth Date
1 EMP_1001 John Peter Male 10-11-1982
2 EMP_1002 Josh Wills Male 09-07-1980
3 EMP_1003 Jeremy Parkar Female 08-09-1990
4 EMP_1004 Aidan Walls Male 05-03-1989
5 EMP_1005 Adam Mondry Male 02-06-1991


SELECT * FROM Employee
WHERE ROWNUM <= 4;

Data displayed from table “Employee” after above Sql Query executed

Id Employee Code First Name Last name Gender Birth Date
1 EMP_1001 John Peter Male 10-11-1982
2 EMP_1002 Josh Wills Male 09-07-1980
3 EMP_1003 Jeremy Parkar Female 08-09-1990
4 EMP_1004 Aidan Walls Male 05-03-1989