Sorting... or ORDERing Your Data

So we've got our database returning our patient records and we can even tell it to only return certain columns. Right now though, the records are being returned in the order they appear in the database. Typically we'll want them sorted by Last Name or maybe the date the patient last visted. Or, perhaps we're looking for patients who are in poorer health and want to quickly see those with fast resting heart rates (pulse). The ORDER BY clause comes to your rescue here. It's really simple to use:

SELECT FirstName, LastName, Pulse, LastVisit FROM Patients
       ORDER BY Pulse DESC

Whoa! What did we just do there? Just a note, in case the multiple lines freak you out... SQL is pretty forgiving of whitespace. So you are free to add spaces, line breaks, and tabs to make the code more readable as we've done here. Now let's focus on the code. I've added an ORDER BY clause to the statement. It simply tells the datbase to order the records on the Pulse column in DESCending order. In the case of numeric fields, that will mean that patients with higher pulse rates will be returned before those with lower pulse rates. So our returned results would look like this:

If we ordered on Pulse ASC (ASC is for ASCending order) with the following statement

SELECT FirstName, LastName, Pulse, LastVisit FROM Patients
       ORDER BY Pulse ASC

NOTE: In the ORDER BY clause, the "ASC" is optional since it is the default sort order. If we excuted the above SELECT statement we'd see these results:

Here are some other possibilities and their results

SELECT FirstName, LastName, Pulse, LastVisit FROM Patients
       ORDER BY LastName

SELECT FirstName, LastName, Pulse, LastVisit FROM Patients
       ORDER BY LastVisit

SELECT FirstName, LastName, Pulse, LastVisit FROM Patients
       ORDER BY LastVisit ASC

SELECT FirstName, LastName, Pulse, LastVisit FROM Patients
       ORDER BY LastVisit DESC

Ordering by Multiple Columns

In most real-life scenarios you will have some data that is the same between records. You may have multiple patients that visited on the same day, for instance. In those cases you might want to order your records by the date visited and, within those dates, you want to order by Pulse. How would you accomplish that?

SELECT FirstName, LastName, Pulse, LastVisit FROM Patients
       ORDER BY LastVisit DESC, Pulse ASC

All I've done is add a comma and another clause "Pulse DESC". If I wanted, I could add more columns to the list this way. The above example might return results like this (note, I've added some additional records so you can see how the sorting works:

<< Retrieving Your Data

Limiting Your Results >>