Home

Lecture 11: ORDER BY

Learning objective

What is ORDER BY?


Use the ORDER BY clause to sort the rows of the SQL query output by specified columns based on their values in ascending or descending order.


A simple example

Sort all columns and rows in the titles table by the value in the title column, numerically in ascending order.

SELECT *
FROM titles
ORDER BY emp_no
;



ASC and DESC order

By default, the order sorted is always ascending, for both numeric columns (smallest -> largest) and text columns (A -> Z). NULL values are sorted last.

This SQL query will yield the same result:

SELECT *
FROM titles
ORDER BY emp_no
;

as this:

SELECT *
FROM titles
ORDER BY emp_no ASC
;

Note that the sorting preference is indicated after the column.

To sort by emp_no in descending order:

SELECT *
FROM titles
ORDER BY emp_no DESC
;

Because ASC and DESC are both SQL reserve keywords, they are capitalized.



ORDER BY with multiple columns

To sort by multiple columns, the columns are separated by commas:

SELECT
    first_name,
    last_name
FROM employees
ORDER BY
    first_name,
    last_name
;

The columns can be sorted in different orders.

Here, we can sort by first name alphabetically ascending (A -> Z) and then last name alphabetically descending (Z -> A).

SELECT
    first_name,
    last_name
FROM employees
ORDER BY
    first_name ASC,
    last_name DESC
;



Shorthand for ORDER BY

Because of the repetitiveness of re-writing the same SQL expression in both the SELECT and the ORDER BY clauses, the columns can be referenced in the ORDER BY using short hand numbers (just like in GROUP BY).

Note that the numbering is based on the order of appearance of the columns as referred in the SQL SELECT clause.

This query:

SELECT
    first_name,
    last_name
FROM employees
ORDER BY
    first_name ASC,
    last_name DESC
;

can be re-written as:

SELECT
    first_name,
    last_name
FROM employees
ORDER BY 1 ASC, 2 DESC
;

since the 1 denotes the 1st column mentioned in the SELECT clause, which is first_name, and the 2 denotes the 2nd column mentioned in the SELECT clause, which is last_name.


Note that the shorthand is particularly helpful with complex SQL expressions:

This query can be shortened:

SELECT
    EXTRACT(year FROM birth_date) AS birth_year,
    EXTRACT(month FROM birth_date) AS birth_month,
    COUNT(DISTINCT emp_no) AS num_employees
FROM employees
GROUP BY
    EXTRACT(year FROM birth_date),
    EXTRACT(month FROM birth_date)
ORDER BY
    EXTRACT(year FROM birth_date),
    EXTRACT(month FROM birth_date)  
;

to this:

SELECT
    EXTRACT(year FROM birth_date) AS birth_year,
    EXTRACT(month FROM birth_date) AS birth_month,
    COUNT(DISTINCT emp_no) AS num_employees
FROM employees
GROUP BY 1, 2
ORDER BY 1, 2
;



Exercises

  1. Fill the ORDER BY clause to sort the outcome alphabetically ascending by title: 
      SELECT
   title,
   COUNT(*) AS cnt_row
  FROM titles
  GROUP BY
   title
  ORDER BY ???
  ;
  2. Fill the ORDER BY clause to sort the outcome from the largest number of row counts per title to the smallest. 
      SELECT
   title,
   COUNT(*) AS cnt_row
  FROM titles
  GROUP BY
   title
  ORDER BY ???
  ;


Order of syntax and execution


Order of syntax

The written syntax order is as follows:

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...
;



Order of execution

Based on our existing vocabulary in SQL:

  1. PostgreSQL identifies the source table using the FROM clause.
  2. Filters rows based on WHERE filter conditions.
  3. Aggregates and groups based on GROUP BY columns.
  4. Additionally filters rows based on aggregated values using HAVING filter conditions.
  5. Returns the columns specified (or temporarily created) using SELECT.
  6. Sorts the order of rows using columns specified in ORDER BY.
  7. Restricts number of rows outputted with LIMIT.

FROM -> [WHERE] -> [GROUP BY] -> [HAVING] -> SELECT -> [ORDER BY] -> [LIMIT]



Style

Please see the SQL style guide for more details.




Breakout exercises

  1. Re-order the following clauses in the right order to create a working SQL query. 
      HAVING COUNT(*) = 15128     
  FROM titles
  LIMIT 3    
  GROUP BY title
  ORDER BY 2 DESC
  SELECT title, COUNT(*) AS cnt_row
  WHERE title IN ('Engineer', 'Manager', 'Senior Engineer')
  2. Find the top 10 most popular first names in the employees table and how many times they occur. (Hint: Use a combination of aggregation, ORDER BY, and LIMIT)


Resources

Wrapping up