TOP clause in SQL SERVER

TOP Definition

TOP clause in a SELECT query indicates that only a specified amount of rows will be returned.

TOP Usage

We use the TOP clause by specifying the number of rows we want to return from a SELECT query.

SELECT TOP(number_of_rows) column1, column2,... FROM table_name

Instead of setting a fixed number of rows, we can set a percent of rows to be returned by using the PERCENT argument:

SELECT TOP(percent_of_rows) PERCENT column1, column2,... FROM table_name

In Some cases and by using the TOP clause in conjuction with the ORDER BY clause, we would like to return all rows that tie for last place of the result set even if it exceeds the speficied limit number. To do that, we use the WITH TIES argument:

SELECT TOP(number_of_rows) WITH TIES column1, column2,... FROM table_name ORDER BY column2

TOP Examples

For our examples, we will use a student table containing 10 rows of data.

student table

To show only the first 3 students:

SELECT TOP(3) * FROM Student
select top records from a student table

To show only the first 37% of students:

select percentage of records from a student table

Note: 37% of 10 students is 3.7 so SQL SERVER will return the next integer value which is 4.

To show the top oldest 3 students and also all other students that tie for the last place:

select top records from a student table using WITH TIES argument

Note: by using the WITH TIES argument, all students that have the same age as the last returned student will also be included in the result set even if it exceeds 3 rows of data.

See also