Limiting the Number of Records Returned
Currently, we're retreiving 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.