SQL TOP Clause

The TOP Clause :
---------------------
The TOP clause is used to specify the number of records to return.

The TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.

Note: Not all database systems support the TOP clause.

SQL Server Syntax :

SELECT TOP number|percent column_name(s)
FROM table_name

SQL SELECT TOP Equivalent in MySQL and Oracle

MySQL Syntax:

SELECT column_name(s)
FROM table_name
LIMIT number

Example :

SELECT *
FROM Persons
LIMIT 5

Oracle Syntax :

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number

Example :

SELECT *
FROM Persons
WHERE ROWNUM <=5


------------------
SQL TOP Example :
The "Persons" table:

P_Id LastName FirstName Address City
-------------------------------------
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
4 Nilsen Tom Vingvn 23 Stavanger

Now we want to select only the two first records in the table above.

We use the following SELECT statement:

SELECT TOP 2 * FROM Persons

The result-set will look like this:

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
---------------------------

SQL TOP PERCENT Example
The "Persons" table:

P_Id LastName FirstName Address City
------------------------------------
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
4 Nilsen Tom Vingvn 23 Stavanger

Now we want to select only 50% of the records in the table above.

We use the following SELECT statement:

SELECT TOP 50 PERCENT * FROM Persons

The result-set will look like this:

P_Id LastName FirstName Address City
------------------------------------
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes