This lecture covers the ORDER BY clause for sorting query results.
When you use a `SELECT` statement, the database returns the rows in whatever order it finds them. There is no guarantee of any particular sequence. To control the order of the output, you need to use the ORDER BY clause.
The ORDER BY clause is used to sort the result set of a query in ascending or descending order.
Default Sorting (Ascending)
Let’s retrieve all of our contacts from the `Contacts` table, but this time, let’s sort them alphabetically by their first name.
SELECT * FROM Contacts ORDER BY FirstName;By default, ORDER BY sorts the data in ascending order (A to Z, 1 to 100). This is the same as writing ORDER BY FirstName ASC, but the ASC keyword is optional.
If you run this query, you will see the results with Emily Jones first, then Jane Doe, then John Smith.
Descending Sort
To sort the data in the reverse order (Z to A, 100 to 1), you use the DESC keyword for descending.
Let’s get the list of contacts, but this time showing the most recently added person first. Since our `ContactID` is auto-incrementing, the highest ID is the newest record. We can sort by `ContactID` in descending order.
SELECT * FROM Contacts ORDER BY ContactID DESC;This will show the contact with the highest ID at the top.
Sorting by Multiple Columns
You can also sort by more than one column. The database will sort by the first column specified, and then if there are any rows with the same value in the first column, it will sort those rows by the second column, and so on.
Let’s sort our contacts by `LastName`. If any contacts have the same last name, we’ll then sort them by `FirstName`.
SELECT * FROM Contacts ORDER BY LastName, FirstName;In this example, if we had two people with the last name “Smith”, the one with the first name “Adam” would appear before the one with the first name “John”.
You can also mix ascending and descending orders:
SELECT * FROM Contacts ORDER BY LastName ASC, ContactID DESC;This would sort all contacts by their last name alphabetically, but if two had the same last name, the one with the higher `ContactID` (the newer one) would be listed first.
In the Next Lecture…
Sometimes you don’t need to see all the results of a query. What if you only want to see the top 5 highest scores, or the 10 most recent customers? In the next lecture, we will learn how to limit the number of rows in our output using the LIMIT clause.
