Welcome to the Learning Center

The Guide | Knowledge Base | FAQ

Browse it all or refine your selection using the filters below on the left.

We hope you like the new Guide we're building for you. Please note the Guide is still under construction and is being actively developed. It should be fully functional soon.

SQL for Beginners - Sorting Data

A beginner's guide to SQL, focusing on core, basic features important to working effectively in XMod Pro: Sorting Data

By: Kelly Ford On: 05/12/2013

Link to this Article
https://dnndev.com/Learn/Guide/Article/SQL-for-Beginners-Sorting-Data

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 database 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:

FirstName LastName Pulse LastVisit
Carlton Johnson 102 10/03/2012
Happy Tucker 78 05/28/2013
Mary Blige 72 01/15/2013
John Smith 68 12/23/2012

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 executed the above SELECT statement we'd see these results:

FirstName LastName Pulse LastVisit
John Smith 68 12/23/2012
Mary Blige 72 01/15/2013
Happy Tucker 78 05/28/2013
Carlton Johnson 102 10/03/2012

Here are some other possibilities and their results

SELECT FirstName, LastName, Pulse, LastVisit FROM Patients
       ORDER BY LastName
FirstName LastName Pulse LastVisit
Mary Blige 72 01/15/2013
Carlton Johnson 102 10/03/2012
John Smith 68 12/23/2012
Happy Tucker 78 05/28/2013
SELECT FirstName, LastName, Pulse, LastVisit FROM Patients
       ORDER BY LastVisit
FirstName LastName Pulse LastVisit
Carlton Johnson 102 10/03/2012
John Smith 68 12/23/2012
Mary Blige 72 01/15/2013
Happy Tucker 78 05/28/2013
SELECT FirstName, LastName, Pulse, LastVisit FROM Patients
       ORDER BY LastVisit ASC
FirstName LastName Pulse LastVisit
Carlton Johnson 102 10/03/2012
John Smith 68 12/23/2012
Mary Blige 72 01/15/2013
Happy Tucker 78 05/28/2013
SELECT FirstName, LastName, Pulse, LastVisit FROM Patients
       ORDER BY LastVisit DESC
FirstName LastName Pulse LastVisit
Happy Tucker 78 05/28/2013
Mary Blige 72 01/15/2013
John Smith 68 12/23/2012
Carlton Johnson 102 10/03/2012

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):

FirstName LastName Pulse LastVisit
Happy Tucker 78 05/28/2013
Carlotta Kim 86 05/28/2013
Steve Barker 95 05/28/2013
Mary Blige 72 01/15/2013
Harold Krupke 86 01/15/2013
John Smith 68 12/23/2012
Carlton Johnson 102 10/03/2012
Next: Limiting Results >>

Articles in this this SQL for Beginners Series:
  • 1 Foundations
  • 2 Retrieving Data
  • 3 Sorting Data
  • 4 Limiting Results
  • 5 Filtering Data