SQL and MySQL course · Module 2: Querying data (SELECT)

Sorting by multiple columns

5 min read ·
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:

tytulrok_wydania
Tom Sawyer2020
Hamlet2020
Pan Tadeusz2015
Dziady2015
……

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:

tytulrok_wydania
Hamlet2020
Tom Sawyer2020
Dziady2015
Pan Tadeusz2015
Etyka2012
Fajdros2012
……

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:

imienazwiskokraj
ArystotelesArystotelesGrecja
EuklidesEuklidesGrecja
HomerHomerGrecja
PlatonPlatonGrecja
SofoklesSofoklesGrecja
AdamMickiewiczPolska
HenrykSienkiewiczPolska
MarkTwainUSA

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:

imienazwiskokraj
MarkTwainUSA
AdamMickiewiczPolska
HenrykSienkiewiczPolska
ArystotelesArystotelesGrecja
EuklidesEuklidesGrecja
HomerHomerGrecja
PlatonPlatonGrecja
SofoklesSofoklesGrecja

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:

tytulrok_wydania
Medea1985
Antygona1985
Ajaks1985
Odyseja1990
Iliada1990
……

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:

idimienazwiskomiasto
…………
1AnnaNowakKraków
3AnnaNowakGdańsk
6JanNowakPoznań
…………

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. 1. How will ORDER BY rok_wydania DESC, tytul ASC behave?

  2. 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):

  1. QuestionSorting by two columns
  2. QuestionRole of the second column in ORDER BY
  3. Coding taskSorting by year and title
  4. Put in orderSorting by country and surname

Useful articles