Sorting data

Intermediate SQL with AI

Jasmin Ludolf

Senior Data Science Content Developer

Explore and analyze data

💻 Build on SQL and AI skills:

Sort Group Filter Summarize

Custom categories

All with prompting!

3D illustration of a speech bubbles, one with AI and one with ellipsis

Film database

Schema showing the four tables in the films database including field names and data types

⚠️ Always get permission before sharing context with AI

Sorting

Arranges data in order
Makes search quicker
Often first step in spotting patterns

Messy bookshelf

Ascending order

Prompt: Show all film titles alphabetically

SELECT title
FROM films
ORDER BY title ASC;

|title                     |
|--------------------------|
|#Horror                   |
|10 Cloverfield Lane       |
|10 Days in a Madhouse     |
|10 Things I Hate About You|
|10,000 B.C.               |
|102 Dalmatians            |
...

Works on any field

ASC Ascending order:
- A to Z
- Special characters, numbers before letters
- Uppercase before lowercase
- Default sorting

Descending order

Prompt: Show all film titles in descending order

SELECT title
FROM films
ORDER BY title DESC;

|title                     |
|--------------------------|
|Æon Flux                  |
|xXx: State of the Union   |
|xXx (Triple X)            |
|eXistenZ|
...

Descending order:
- Z to A
- Lowercase before uppercase
- Æ may come first if stored after z

DESC keyword for descending

Specify the order

⬆ Ascending order

Oldest first
Alphabetical
From A to Z
Chronological

⬇ ️ Descending order

Newest first
From Z to A
Counting down

Vague prompts

SELECT title
FROM films
ORDER BY release_year;

|title                         |
|------------------------------|
|Intolerance: Love's Struggl...|
|Over the Hill to the Poorhouse|
|The Big Parade                |
|Metropolis                    |
...

Being specific

Prompt: Show titles and release years, sorted by release year

SELECT title, release_year
FROM films
ORDER BY release_year;

|title                         |release_year|
|------------------------------|------------|
|Intolerance: Love's Struggle  |1916        |
|Over the Hill to the Poorhouse|1920        |
|The Big Parade                |1925        |
|Metropolis                    |1927        |
...

Sorting multiple fields

Sorts by first listed field, then the next

SELECT title, oscar
FROM awards
ORDER BY oscar DESC;

|title                          |oscar|
|-------------------------------|-----|
|Lord of the Rings:Return of ...|11   |
|Titanic                        |11   |
|Ben-Hur                        |11   |

Like a tie-breaker

SELECT title, oscar, bafta
FROM awards
ORDER BY oscar DESC, bafta DESC;

|title               |oscar|bafta|
|--------------------|-----|-----|
|Lord of the Rings...|11   |2    |
|Ben-Hur             |11   |1    |
|Titanic             |11   |0    |

Different directions

Prompt: Show film titles and release years sorted by year descending then title ascending

SELECT title, release_year
FROM films
ORDER BY release_year DESC, title ASC;

|title           |release_year|
|----------------|------------|
|10,000 B.C.     |            |
|A Touch of Frost|            |    
|Anger Management|            |    
|Animal Kingdom  |            |
...

Let's practice!

Intermediate SQL with AI