SQL Commands


SQL SELECT TOP Tutorial

The SELECT TOP clause is used to return the top X numbers or N Percent row from the table. Only MSSQL server and MS Access database support the SELECT TOP clause.

To fetch limited number of records, LIMIT clause is used in MySQL database & ROWNUM in Oracle database. You can specify “number of rows” or “percentage of rows” after TOP keyword.

Syntax of a SQL TOP CLAUSE

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE [condition];

Note: WHERE condition is optional to use along with SELECT TOP Clause.

SQL SELECT TOP Clause Example:

Database table “Employee”

Employee IDEmployee NameAgeGenderLocationSalary
1001Henry54MaleNew York100000
1002Tina36FemaleMoscow80000
1003John24MaleLondon40000
1004Mile31MaleLondon70000
1005Tara26FemaleMoscow50000
1006Sohpie29FemaleLondon60000

MSSQL Server and MS Access Example: Statement which would fetch the top 3 records from “Employee” table:
The user can specify number of rows or % of rows

SELECT TOP 3 * FROM Employee;

or

SELECT TOP 50 PERCENT * FROM Employee;

If one is using MySQL, then here is an equivalent example

SELECT * FROM Employee
LIMIT 3;

If one is using ORACLE, then here is an equivalent example

SELECT * FROM Employee
WHERE ROWNUM <= 3;

SQL TOP CLAUSE Output

All these three commands would give the following results in respective database.

Employee IDEmployee NameAgeGenderLocationSalary
1001Henry54MaleNew York100000
1002Tina36FemaleMoscow80000
1003John24MaleLondon40000

When is SQL SELECT TOP Useful?

Top (MySql Limit, Oracle RowNum) is one of the most useful commands being used when selecting and filtering rows. This TOP keyword is important when we are not interested in getting all of the rows fulfilling the condition.

This keyword is also used when finding out the highest (n-highest), lowest(n-lowest) data from table.



BookMark This Page




EnglishFrenchGermanItalianPortugueseRussianSpanish