SELECT TOP Clause

The SELECT TOP clause in SQL only returns the specified number of rows from the table. It is valuable on enormous tables with a large number of records. Returning countless records can affect execution.

• SQL TOP Clause is used in SQL Server and Sybase to limit the number of records returned.

Syntax:

SELECT TOP count column1, column2, ...
FROM table_name;
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];

• column1, column2: names of columns.
• count: number of records to be fetched.
• WHERE conditions: Optional. Filters the data based on conditions.
• ORDER BY expression: Optional. Sorts the result set in ascending or descending order.

Example:

Table: Student

Example 1: Using SELECT TOP Clause in SQL

Query:

SELECT TOP 3 *FROM Student;

Output:

Example 2: SQL SELECT TOP with ORDER BY Clause

Query:

SELECT TOP 3 * FROM Student;
ORDER BY Marks DESC;

Output:

Example 3: SQL SELECT TOP Clause with WHERE Clause

Query:

SELECT TOP 2 
*FROM Student 
WHERE Branch = 'CSE' 
ORDER BY Marks DESC;

Output:

Example 4: SQL SELECT TOP PERCENT Clause

Query:

SELECT TOP 50 PERCENT 
*FROM Student;

Output:

Example 5: SQL TOP PERCENT with WHERE Clause

Query:

SELECT TOP 50 PERCENT 
*FROM Student 
WHERE Marks > 80 
ORDER BY Marks;

Output:

The SQL TOP keyword is utilized with these database systems:

• SQL Server
• MS Access

NOTE: 

To get the same functionality on MySQL and Oracle databases there is a bit of difference in the basic syntax;

Equivalent Syntaxes are as follows:

• For MySQL databases:

  SELECT column1, column2
  FROM table_name
  ORDER BY Marks DESC
  LIMIT value;
  column1 , column2: fields int the table
  table_name: name of table
  value: number of rows to return from top

• For Oracle databases:

  SELECT column1, column2
  FROM table_name
  FETCH FIRST value ROWS ONLY;
  column1 , column2: fields int the table
  table_name: name of table
  value: number of rows to return from top