ORDER BY

An introduction to the ORDER BY clause.

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:

year month count animal_type
2013 10 542 Cat
2013 11 436 Cat
2013 12 331 Cat
2014 1 335 Cat
2014 2 269 Cat
2014 3 353 Cat
2014 4 566 Cat
2014 5 901 Cat
2014 6 821 Cat
2014 7 881 Cat

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:

year month count animal_type
2015 6 1103 Cat
2015 5 1009 Cat
2016 5 921 Cat
2017 5 914 Cat
2014 5 901 Cat
2017 6 895 Cat
2014 7 881 Cat
2014 6 821 Cat
2015 8 812 Cat

There is also a modifier ASC for ordering data. ASC is used in ORDER BY clauses to indicate that the results should be sorted in ascending order. However it is a redundant modifier as ascending order is the default for SQL.

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:

year month count animal_type
2017 12 100 Cat
2017 11 427 Cat
2017 10 513 Cat
2017 9 656 Cat
2017 8 565 Cat
2017 7 669 Cat
2017 6 895 Cat
2017 5 914 Cat

If there are cells in the table with NULL values, they are included in your sort and by default show up last. If you want to force them to show up first, add the modifier NULLS FIRST either after the column name or after DESC if you’re sorting in descending order
:

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
monthyear animal_type outcome_type outcome_subtype sex_upon_outcome age_upon_outcome
2013-10 Cat Adoption Neutered Male 1 month
2013-10 Cat Adoption Intact Female 1 month
2013-10 Cat Adoption Intact Female 1 month
2013-10 Cat Adoption Intact Male 1 month
2013-10 Cat Adoption Spayed Female 1 month

There is also a modifier NULLS LAST, however it is redundant as putting nulls last in an order-by clause is the default for SQL
.