Limiting the Number of Records Returned

Currently, we're retrieving every record in our table. If we have 10,000 patient records, our users aren't going to be very happy if we try to display all of them. Typically, you'll want to grab about 10-20 records at a time. So, how would we retrieve 10 records? You might be tempted to modify our SELECT statement like this:

SELECT FIRST 10 FROM Patients

That's pretty darn close. Subsitute "TOP" for "FIRST"

SELECT TOP 10 FROM Patients

Better but still not right. Does something seem missing from the statement? Something we've used in our previous examples? If you guessed "columns" then you get a gold star for remembering you need to tell the SELECT command what columns you want to retrieve. This is no different if we're limiting the number of records we're getting. So, let's modify our command again:

SELECT TOP 10 FirstName, LastName, Pulse, LastVisit FROM Patients

Ding! Ding! We have a winner. This command will only retrieve the first or TOP 10 records from our table in whatever order they appear in the table. If we add an ORDER BY clause (as we learned about in our previous lesson) on LastVisit DESC, then we'll get the 10 most recent patient visits.

Another important point to remember: The TOP clause operates on whatever records are returned from the rest of your SELECT command. So, things like ORDERing and filtering using the WHERE clause will execute first. Once you have a set of results, then the TOP clause will return XX number of records from that set.

Next: Filtering Data >>

Articles in this this SQL for Beginners Series:

• 1 Foundations
• 2 Retrieving Data
• 3 Sorting Data
• 4 Limiting Results
• 5 Filtering Data