SQL and MySQL course · Module 2: Querying data (SELECT)
Sorting by multiple columns
In this lesson6
One field is sometimes not enough. When many scrolls share the same value - for instance several books published in the same year - you need a second criterion to break the tie.
A tie on the shelf
Recall the query from the previous lesson that arranges the books newest first:
SELECT tytul, rok_wydania
FROM ksiazki
ORDER BY rok_wydania DESC;At the very top of the result stand two books from 2020:
| tytul | rok_wydania |
|---|---|
| Tom Sawyer | 2020 |
| Hamlet | 2020 |
| Pan Tadeusz | 2015 |
| Dziady | 2015 |
| … | … |
Why does "Tom Sawyer" stand before "Hamlet"? For no reason. The Library sorted only by year, and their year is the same. The order of tied scrolls is a matter of chance - tomorrow it may be the other way round.
Two fields separated by a comma
In ORDER BY you can give several fields, separating them with a comma. Let us add the title as the second criterion:
SELECT tytul, rok_wydania
FROM ksiazki
ORDER BY rok_wydania DESC, tytul ASC;Now the title breaks the ties:
| tytul | rok_wydania |
|---|---|
| Hamlet | 2020 |
| Tom Sawyer | 2020 |
| Dziady | 2015 |
| Pan Tadeusz | 2015 |
| Etyka | 2012 |
| Fajdros | 2012 |
| … | … |
The Library sorts first by the first field (rok_wydania descending). Only when several books came out in the same year does it reach for the second field (tytul ascending) and line them up alphabetically. The order of the years has not changed - only the order within each year has.
Each field has its own direction
Each column may have its own ASC or DESC. The direction stands right after the field name and applies only to it. Here both fields ascend:
SELECT imie, nazwisko, kraj
FROM autorzy
ORDER BY kraj ASC, nazwisko ASC;First we group the authors by country (alphabetically), and within each country we arrange them ascending by surname:
| imie | nazwisko | kraj |
|---|---|---|
| Arystoteles | Arystoteles | Grecja |
| Euklides | Euklides | Grecja |
| Homer | Homer | Grecja |
| Platon | Platon | Grecja |
| Sofokles | Sofokles | Grecja |
| Adam | Mickiewicz | Polska |
| Henryk | Sienkiewicz | Polska |
| Mark | Twain | USA |
Directions can also be mixed. Let us reverse the order of the countries and keep the surnames ascending:
SELECT imie, nazwisko, kraj
FROM autorzy
ORDER BY kraj DESC, nazwisko ASC;The countries now go from the end of the alphabet, but within each country the surnames still ascend:
| imie | nazwisko | kraj |
|---|---|---|
| Mark | Twain | USA |
| Adam | Mickiewicz | Polska |
| Henryk | Sienkiewicz | Polska |
| Arystoteles | Arystoteles | Grecja |
| Euklides | Euklides | Grecja |
| Homer | Homer | Grecja |
| Platon | Platon | Grecja |
| Sofokles | Sofokles | Grecja |
The most common mistake
Since a direction applies to one field only, it is easy to slip up. Someone wanted everything descending and wrote DESC once, at the very end:
SELECT tytul, rok_wydania
FROM ksiazki
ORDER BY rok_wydania, tytul DESC;Here the word DESC applies only to the title. The year has no direction, so the year still ascends:
| tytul | rok_wydania |
|---|---|
| Medea | 1985 |
| Antygona | 1985 |
| Ajaks | 1985 |
| Odyseja | 1990 |
| Iliada | 1990 |
| … | … |
The oldest books stand at the top, not the newest. Only the titles within each year are descending. If both fields should descend, write DESC at each one: ORDER BY rok_wydania DESC, tytul DESC.
When both fields tie
Sometimes even two fields are not enough. On the readers' shelf there are two people with the same first name and surname. That is why we add a third field at the end, id, which is different in every row:
SELECT id, imie, nazwisko, miasto
FROM czytelnicy
ORDER BY nazwisko ASC, imie ASC, id ASC;Here is a part of the result around the Nowaks:
| id | imie | nazwisko | miasto |
|---|---|---|---|
| … | … | … | … |
| 1 | Anna | Nowak | Kraków |
| 3 | Anna | Nowak | Gdańsk |
| 6 | Jan | Nowak | Poznań |
| … | … | … | … |
Without id both Anna Nowaks would tie on both fields and could swap places. With id their order is fixed once and for all.
Why break ties?
- The result becomes predictable - ties are settled by the second field, not by chance. If that one repeats too, add a third, e.g.
id. - Reports are clearer - the data has a distinct, two-level order.
My advice: before you hand in a report, look at the first field in ORDER BY and consider whether any value can repeat in it. If so, add a second one. In the next lesson you will combine sorting with LIMIT and build your first rankings - there a chance tie can decide who makes it into the top three.
Remember: the first field rules, and the next ones step in only on a tie. That is how a scribe tidies even the most crowded shelf.
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. How will ORDER BY rok_wydania DESC, tytul ASC behave?
2. Why do you give a second column in ORDER BY, e.g. ORDER BY kraj ASC, nazwisko ASC?
Hands-on tasks in the game
- Code editor
Write a query that retrieves tytul and rok_wydania from the ksiazki table and sorts the result in descending order by rok_wydania, and for the same year ascending by tytul.
- Horizontal ordering
Arrange the elements in the correct order to build a clause sorting authors by country, then by surname (left to right):