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.

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|
...

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  |
...

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 |
...

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        |
...

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       |

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     |
...

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;

Let's practice!

Intermediate SQL