Sorting results

Intermediate SQL

Jasmin Ludolf

Data Science Content Developer, DataCamp

Sorting results

An illustration showing two side by side wardrobe, the left has a messy pile of clothes while the right shows an organized wardrobe.

Intermediate SQL

ORDER BY

SELECT title, budget
FROM films
ORDER BY budget;
|title               |budget|
|--------------------|------|
|Tarnation           |218   |
|My Date with Drew   |1100  |
|A Plague So Pleasant|1400  |
|The Mongol King     |3250  |
...
SELECT title, budget
FROM films
ORDER BY title;
|title                     |budget  |
|--------------------------|--------|
|#Horror                   |1500000 |
|10 Cloverfield Lane       |15000000|
|10 Days in a Madhouse     |12000000|
|10 Things I Hate About You|16000000|
...
Intermediate SQL

ASCending

SELECT title, budget
FROM films
ORDER BY budget ASC;
|title               |budget|
|--------------------|------|
|Tarnation           |218   |
|My Date with Drew   |1100  |
|A Plague So Pleasant|1400  |
|The Mongol King     |3250  |
...
Intermediate SQL

DESCending

SELECT title, budget
FROM films
ORDER BY budget DESC;
|title                         |budget|
|------------------------------|------|
|Love and Death on Long Island |null  |
|The Chambermaid on the Titanic|null  |
|51 Birch Street               |null  |
...
SELECT title, budget
FROM films
WHERE budget IS NOT NULL
ORDER BY budget DESC;
|title                  |budget     |
|-----------------------|-----------|
|The Host               |12215500000|
|Lady Vengeance         |4200000000 |
...
Intermediate SQL

Sorting fields

SELECT title
FROM films
ORDER BY release_year;
|title                                |
|-------------------------------------|
|Intolerance: Love's Struggle Throu...|
|Over the Hill to the Poorhouse       |
|The Big Parade                       |
|Metropolis                           |
...
SELECT title, release_year
FROM films
ORDER BY release_year;
|title                   |release_year|
|------------------------|------------|
|Intolerance: Love's S...|1916        |
|Over the Hill to the ...|1920        |
|The Big Parade          |1925        |
|Metropolis              |1927        |
...
Intermediate SQL

ORDER BY multiple fields

  • ORDER BY field_one, field_two
SELECT title, wins
FROM best_movies
ORDER BY wins DESC;
|title                           |wins|
|--------------------------------|----|
|Lord of the Rings:Return of t...|11  |
|Titanic                         |11  |
|Ben-Hur                         |11  |
  • Think of field_two as a tie-breaker
SELECT title, wins, imdb_score
FROM best_movies
ORDER BY wins DESC, imdb_score DESC;
|title                |wins|imdb_score|
|---------------------|----|----------|
|Lord of the Rings:...|11  |9         |
|Ben-Hur              |11  |8.1       |
|Titanic              |11  |7.9       |
Intermediate SQL

Different orders

SELECT birthdate, name
FROM people
ORDER BY birthdate, name DESC;
|birthdate |name            |
|----------|----------------|
|1990-01-01|Robert Brown    |
|1990-02-02|Anne Smith      |
|1991-05-14|Amy Miller      |
|1991-11-22|Adam Waters     |
...
Intermediate SQL

Order of execution

-- Written code:

SELECT item FROM coats WHERE color = `yellow` ORDER BY length LIMIT 3;
-- Order of execution:

SELECT item
FROM coats
WHERE color = `yellow`
ORDER BY length
LIMIT 3;
Intermediate SQL

Let's practice!

Intermediate SQL

Preparing Video For Download...