SQL SELECT TOP Statement

SQL SELECT TOP statement returns the specified number of records starting from the top of the table.

The SQL SELECT TOP Syntax

Selects top n rows (with the specified columns data) from the table:

SELECT TOP n column_name1, column_name2
  FROM table-name;

Selects top n complete rows from the table:

SELECT TOP n *
  FROM table-name;

Selects the given percentage of number of rows from top
For example: 50 PERCENT would return half of the rows from top.

SELECT TOP value PERCENT column_name1, column_name2 
  FROM table_name;

SELECT TOP n for multiple columns

Table: STUDENT

STU_ID      STU_NAME     STU_AGE     STU_DEPT
------      --------     -------     --------
1002        Chaitanya      29         CSE
1003        Ajeet          28         ECE
1004        Rahul          27         ME
1005        Shivam         28         CL
1006        Patrick        26         ECE

Query:

SELECT TOP 3 STU_ID, STU_DEPT
  FROM STUDENT

Output:

STU_ID      STU_DEPT
------      --------
1002        CSE
1003        ECE
1004        ME

Explanation:
The Query fetched the top 3 rows having the data of specified columns.

SELECT TOP n entire table

Table: Same as above example.

Query:

SELECT TOP 2 *
  FROM STUDENT

Output:

STU_ID      STU_NAME     STU_AGE     STU_DEPT
------      --------     -------     --------
1002        Chaitanya      29         CSE
1003        Ajeet          28         ECE

Explanation:
The Query fetched the top 2 rows of the table.

SELECT TOP value PERCENT of rows from table

Table: Same as above example.

Query:

SELECT TOP 20 PERCENT *
  FROM STUDENT

Output:

STU_ID      STU_NAME     STU_AGE     STU_DEPT
------      --------     -------     --------
1002        Chaitanya      29         CSE

Explanation:
The Query fetched the top 20% of the rows from the table. Since table has only 5 rows, the 20% of 5 is 1 so the output has only single top row.

Why use the SELECT TOP Statement?

This statement is generally used when the size of the database is large and we do not want to fetch all the rows from the database because it may take huge amount of time to fetch all the rows every time.

LIMIT and ROWNUM

The TOP clause that we have seen above is used for the SQL Server. Non-SQL server databases such as MySQL & Oracle uses LIMIT & ROWNUM clauses respectively in place of TOP keyword to fetch the limited number of rows from large databases.