7. The Order By Clause

We've learned how to write a basic select statement, choose some columns to include in our output, and limit the rows using some logic in our where clause. The last basic clause we're going to cover is the order by clause. order by does what the name implies: it sorts your results by one or more column values or expressions. Some notes about order by:

• order by works with data of all types: dates, text, and numbers.
• You can use order by to sort your results by any column value present in your select clause, or by any valid expression (e.g. case statements or non-aggregate functions – we will cover these later).
• order by is applied at the very end of the process, after your database has executed the entirety of your query and received all the results. All it does is sort your results, it does not have any impact on which rows or columns are included in your results.
• You can sort your data as ascending using the asc keyword, or descending using the desc keyword. You can also omit this keyword altogether and by default the database will sort your data ascending.

Usage of order by is straightforward. Simply list which columns/expressions your data should be sorted by, and the database will take care of the rest. Here's a simple example:

select
    first_name
    ,last_name
from accounts
order by last_name asc

This query will return the first and last name values from our accounts table and sort the results by last_name, ascending (i.e., A names first, then B names, etc.) We can also order by multiple columns:

select
    first_name
    ,last_name
from accounts
order by last_name asc
    ,first_name asc

This query is the same as the first one, but it will first sort the rows by last name ascending, and then by first name ascending (so for example Aaron Jones will come before Patricia Jones).

Exercise 1: Simple Sorting

Write an SQL query that outputs the transaction_id and amount of all transactions in the database, sorted from highest amount to lowest amount.

Exercise 2: Advanced Sorting

Write an SQL query that outputs every column from the addresses table (using select star), sorted by state ascending and zip code descending.