SQL and MySQL course Β· Module 2: Querying data (SELECT)
ORDER BY - arranging the scrolls
In this lesson3
A raw shelf returns scrolls in no particular order - the order in which they happen to lie. Today it may be the order in which they were added to the catalog; tomorrow, after tidying the shelves, a completely different one. Meanwhile a reader at the desk asks: "Show me the books, newest first". ORDER BY lets you arrange the scrolls - from oldest, from cheapest, alphabetically - however your heart desires.
Ascending and descending
We add ORDER BY after the shelf name, and after it we give the field by which the Library should arrange the rows. By default the sorting is ascending (ASC, from ascending). If you give no direction, the Library will still arrange the scrolls ascending - the query below gives the same result with ASC and without it:
SELECT tytul, rok_wydania
FROM ksiazki
ORDER BY rok_wydania ASC;The oldest editions stand at the top. Here are the first five rows of seventeen:
| tytul | rok_wydania |
|---|---|
| Antygona | 1985 |
| Medea | 1985 |
| Ajaks | 1985 |
| Iliada | 1990 |
| Odyseja | 1990 |
| β¦ | β¦ |
Three books were published in 1985. Their order among themselves is not fixed - the Library may give them in a different order. You will see how to break such ties in the next lesson.
To reverse the order - from largest to smallest - use DESC (from descending). Only the last word changes:
SELECT tytul, rok_wydania
FROM ksiazki
ORDER BY rok_wydania DESC;This query shows the books newest first, which is exactly what the reader asked for:
| tytul | rok_wydania |
|---|---|
| Tom Sawyer | 2020 |
| Hamlet | 2020 |
| Pan Tadeusz | 2015 |
| Dziady | 2015 |
| Etyka | 2012 |
| β¦ | β¦ |
Numbers are arranged by value: 2020 is greater than 2015, so it stands higher. And this is how you arrange books cheapest first:
SELECT tytul, cena
FROM ksiazki
ORDER BY cena ASC;The cheapest copies are at the top of the list:
| tytul | cena |
|---|---|
| Ajaks | 18.50 |
| Antygona | 19.90 |
| Medea | 21.00 |
| Dziady | 24.50 |
| Hamlet | 27.00 |
| β¦ | β¦ |
Text and empty fields
MySQL arranges text alphabetically. With its default settings MySQL 8 does not distinguish uppercase from lowercase letters here, so "hamlet" and "Hamlet" end up side by side. Empty fields, that is NULL, are treated by MySQL as smaller than any other value: with ASC they come first, and with DESC they come last.
Where does ORDER BY stand?
Always after FROM, near the end of the query. This is how you arrange the authors alphabetically by surname:
SELECT imie, nazwisko FROM autorzy ORDER BY nazwisko ASC;The surnames stand in alphabetical order, from Arystoteles to Twain:
| imie | nazwisko |
|---|---|
| Arystoteles | Arystoteles |
| Euklides | Euklides |
| Homer | Homer |
| Adam | Mickiewicz |
| Platon | Platon |
| Henryk | Sienkiewicz |
| Sofokles | Sofokles |
| Mark | Twain |
Two mistakes happen here most often:
- ORDER BY before FROM.
SELECT imie ORDER BY nazwisko FROM autorzy;is a syntax error. First point to the shelf, only then arrange its scrolls. - The direction before the field.
ORDER BY DESC nazwiskowill not run either. The direction always stands after the field name:ORDER BY nazwisko DESC.
Remember also what ORDER BY does not do. The scrolls do not change places on the shelves - the shelf stays as it was. Only the answer is sorted, and only for the span of this one query.
My advice: when the order matters, always add ORDER BY, even if the result comes out right "anyway". The fact that the scrolls lie in order today does not mean they will lie the same way tomorrow. You may leave out ASC, but in reports I write it for clarity.
ORDER BY is the scribe's hand that tidies scattered scrolls upon the shelf. In the next lesson you will learn to sort by several fields at once.
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What does the word DESC mean in the ORDER BY clause?
2. What is the default sort direction when you give neither ASC nor DESC in ORDER BY?
Hands-on tasks in the game
- Vertical ordering
Arrange the clauses of a sorting query in the correct order in which we write them in MySQL (top to bottom):
- Code editor
Write a query that retrieves the columns tytul and rok_wydania from the ksiazki table and sorts the result in descending order by year of publication (newest first).
- Click in order
Click the elements in the order in which you write a clause sorting books ascending by price:
- Code editor
Write a query that retrieves tytul and cena from the ksiazki table and sorts the result in ascending order by price (cheapest first).