Structured Query Language tutorial ( top clause )

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