Understanding behaviour of order by in a query

February 25, 2024

I aim to select full name of employees that either have no boss or their boss lives on a street that contains letter ‘o’ or letter ‘u’. Then, I want to list them in descending order by full name.
The problem comes in the ordering, because by queries that I think are the same I get different answers.

When I introduce in MySQL Workbench the following command:

select concat(surnames,', ',name) as 'Full Name', street
from employee
where boss is null or (boss is not null and (street like'%u%' or steet like '%o%'))
order by concat(surnames,', ',name) desc;

By this command I get the answer I want, that is:

Full Name              Street
Suárez García, Juan Juan Valdés 25
Sarasola Goñi, Vanesa   Austria
Requena Santos, Pilar   Alicante 3
Puertas Elorza, Marta   Lope de Vega 3
Piedra Trujillo, Ramón  Madre Vedruna 21
Narváez Alonso, Alba    Vara de Rey 22
Gómez de la Sierra, Francisco   Loscertales 9
Chávarri Díez, Lorea    
Arrieta Alcorta, Kepa   Urbieta 33
Álvarez González, Ana   Graus 19

But when I change the ordering by another that looks the same to me:

select concat(surnames,', ',name) as 'Full Name', street
from employee
where boss is null or (boss is not null and (street like'%u%' or steet like '%o%'))
order by 'FullName' desc;

I get a wrong answer that looks like:

Full Name               Street
Suárez García, Juan Juan Valdés 25
Puertas Elorza, Marta   Lope de Vega 3
Chávarri Díez, Lorea    
Narváez Alonso, Alba    Vara de Rey 22
Gómez de la Sierra, Francisco   Loscertales 9
Piedra Trujillo, Ramón  Madre Vedruna 21
Sarasola Goñi, Vanesa   Austria
Requena Santos, Pilar   Alicante 3
Álvarez González, Ana   Graus 19
Arrieta Alcorta, Kepa   Urbieta 33

Can somebody tell me what’s going on here?

>Solution :

When you define an alias, you can use either single-quotes, as if it’s a string literal, or you can use back-ticks, as if it’s an identifier. But when you refer to the alias later in the query, you must treat it as an identifier.

order by 'FullName' desc;

This orders by the constant string value 'FullName', not the alias. Ordering by a constant value or expression makes every row tied with every other row, and the resulting order is undefined.

If you want to refer to the identifier, use back-ticks: