SQL Top

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

Copy to Clipboard
SELECT TOP 3 * FROM Individual

Source Table

IndividualIdFirstNameLastNameUserName
1FredFlinstonefreddo
2HomerSimpsonhomey
3HomerBrownnotsofamous
4OzzyOzzbournesabbath
5HomerGainnoplacelike

Result

IndividualIdFirstNameLastNameUserName
1FredFlinstonefreddo
2HomerSimpsonhomey
3HomerBrownnotsofamous

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

Copy to Clipboard
SELECT TOP 40 PERCENT * FROM Individual

Result

IndividualIdFirstNameLastNameUserName
1FredFlinstonefreddo
2HomerSimpsonhomey

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

Copy to Clipboard
SELECT TOP 40 PERCENT * FROM Individual
ORDER BY LastName DESC

Result

IndividualIdFirstNameLastNameUserName
2HomerSimpsonhomey
4OzzyOzzbournesabbath

Enjoy this page?

  1. Link to this page (copy/paste into your own website or blog):
  2. Link to Quackit using one of these banner ads.

Thanks for supporting Quackit!

Sponsored Link: Design CODE-FREE Websites

Check a Domain:

ZappyHost Logo