SQL 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.

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 6

Oracle Syntax

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

Example

SELECT *
FROM Persons
WHERE ROWNUM <=6

SQL TOP Example

The "Persons" table:

 

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

SQL TOP PERCENT Example

The "Persons" table:

 

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:

Note: Not all database systems support the TOP clause.

SQL TOP Clause