sq6

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

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike

Result

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous

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

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey

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

IndividualId	FirstName	LastName	UserName
2	Homer	Simpson	homey
4	Ozzy	Ozzbourne	sabbath