ORDER BY

The order that data shows up in a table is completely random and, more often than not, this isn’t the order in which we’d like to see it when we run our queries. Fortunately, SQL has an ORDER BY clause. ORDER BY allows you to sort the results of a query based on a specific column or group of columns.

In the last section on WHERE, to show us just the data on cats, we ran the query:

SELECT year,
       month,
       count,
       animal_type
  FROM austin_animal_center_intakes_by_month
 WHERE animal_type = "Cat"

If we would like to get the data back ordered first by year and then by month we could add an ORDER BY clause like this:

SELECT year,
       month,
       count,
       animal_type
  FROM austin_animal_center_intakes_by_month
 WHERE animal_type = "Cat"
 ORDER BY year, month

and the first few rows of the table returned would look like this:

We can also flip the order of the values returned by ORDER BY by using the modifier DESC. DESC is used in ORDER BY clauses to indicate that the results should be sorted in descending order. Here’s another variation on the cat query ordered by the number of cats taken in in a month from greatest number to smallest:

SELECT year,
       month,
       count,
       animal_type
  FROM austin_animal_center_intakes_by_month
 WHERE animal_type = "Cat"
 ORDER BY count DESC

Then the results returned by the query would look like this:

ORDER BY is particularly useful when you have several columns you would like to sort in a specific order, and you can indicate for each column whether you want the values in ascending or descending order. If we wanted to run the previous query showing the most recent data first we would write the query like this:

SELECT year,
       month,
       count,
       animal_type
  FROM austin_animal_center_intakes_by_month
 WHERE animal_type = "Cat"
 ORDER BY year DESC, month DESC

As expected, the resulting table would begin with December 2017 data:

SELECT austin_animal_center_outcomes.monthyear,
       austin_animal_center_outcomes.animal_type,
       austin_animal_center_outcomes.outcome_type,
       austin_animal_center_outcomes.outcome_subtype,
       austin_animal_center_outcomes.sex_upon_outcome,
       austin_animal_center_outcomes.age_upon_outcome
  FROM austin_animal_center_outcomes
 WHERE austin_animal_center_outcomes.outcome_type = "Adoption"
       AND austin_animal_center_outcomes.animal_type = "Cat"
 ORDER BY austin_animal_center_outcomes.outcome_subtype NULLS FIRST

Exercises (Continued from previous section)

Exercise 7

Patient weight is stored as one of the descriptions in the observations table as Body Weight. Write a query against the observations table that returns all the records that have Body Weight in the description column and order the results lightest to heaviest.

Try query

Exercise 8

Using the date, patient, description, value, and units columns from the observations table, write a query that lists all the patients from tallest to shortest.

Try query