The TOP clause allows you to specify how many results are returned in your query.

In the preceeding lessons on the SELECT statement, the examples have returned all records that have matched our SELECT criteria. This is great if you want to look at every record, but, what if you only want to look at the first few records?

Sounds like you need the SQL TOP clause.

The TOP clause allows us to specify how many rows to return. This can be useful on very large tables when there are thousands of records. Returning thousands of records can impact on performance, and if you are working with a production database, this could have an adverse impact on the users.

Note: The SQL TOP clause is Transact-SQL, and not part of ANSI SQL. Therefore, depending on your database system, you may not be able to use this clause.

SQL statement

SELECT TOP 3 * FROM Individual;

Source Table

IndividualIdFirstNameLastNameUserName1FredFlinstonefreddo2HomerSimpsonhomey3HomerBrownnotsofamous4OzzyOzzbournesabbath5HomerGainnoplacelike

Result

IndividualIdFirstNameLastNameUserName1FredFlinstonefreddo2HomerSimpsonhomey3HomerBrownnotsofamous

Specifying a Percentage

You have the option of specifying a percentage of the result set instead of an absolute value. You do this with the PERCENT keyword.

SQL statement

SELECT TOP 40 PERCENT * FROM Individual;

Result

IndividualIdFirstNameLastNameUserName1FredFlinstonefreddo2HomerSimpsonhomey

SQL TOP and the ORDER BY clause

If you are using the TOP clause along with the ORDER BY clause, the TOP clause is applied to the ordered result set.

Therefore, if we add an ORDER BY to the above query, we end up with something like this:

SQL statement

SELECT TOP 40 PERCENT * FROM Individual

ORDER BY LastName DESC;

Result

IndividualIdFirstNameLastNameUserName2HomerSimpsonhomey4OzzyOzzbournesabbath