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_nameInstead 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_nameIn 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 column2TOP Examples
For our examples, we will use a student table containing 10 rows of data.
To show only the first 3 students:
SELECT TOP(3) * FROM Student
To show only the first 37% of students:
SELECT TOP(37) PERCENT * FROM Student
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(3) WITH TIES * FROM Student ORDER BY Age
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.