How to sort the data of a query by predefined values?

Question

Using as an example, I have a certain user table, where that user’s city is, is a reference to another table.

I need to order these users in a query according to the location of the current logged in user. Example, if I live in Belo Horizonte, the user data of the Belo Horizonte.

I know that in Mysql the ORDER BY acts as a means of sorting by the value in the column, but I don’t know how to sort by specific values.

For example, I want the order to be: Belo Horizonte and from then on it makes no difference.

In a fictional example I’ll show you what I want:

First I take the city’s value:

$valor = Auth::user()->cidade->nome; // 'Belo Horizonte'

Then I want the ordering of records to start with users who have the same city and, after that, be indifferent.

SELECT * FROM usuarios JOIN cidade.id = usuarios.cidade_id
ORDER BY <cidade.nome COMEÇANDO DE $valor>

^{Fictional code - I know it would make a syntax error, it’s just a demonstration of what I want.}

Is there any way to sort the data of a query, other than by the table field, but by predefined specified values?

Answer 1

It would be something like that:

SELECT * FROM usuarios 
INNER JOIN estados e on e.id = usuarios.estado_id
ORDER BY e.sigla = 'MG' DESC, e.sigla

The reference is here.

Answer 2

Yeah, you can do something like that:

SELECT * 
  FROM usuarios 
INNER JOIN estados
   ON estados.id = usuarios.estado_id
ORDER BY CASE WHEN estados.nome = $valor THEN 1 ELSE 2 END, estados.nome  

The logic is quite simple, if the user state is the one indicated in the $value variable, then we assign the value 1 (indicating higher priority), otherwise we assign 2. After that you can use any other column to sort the remaining records. In this case I order by alphabetical order of states.

Here’s the Sqlfiddle to see how it works in practice.

Answer 3

Yes, you can specify literally the values you want to appear first in the query:

SELECT * 
  FROM usuarios 
INNER JOIN estados
  ON estados.id = usuarios.estado_id
ORDER BY estados.sigla = 'MG', estados.sigla asc

Answer 4

Would be like this, if you have Acre and Alagoas, will order by the same, if you have neither of the two will order by Amazon :

SELECT * FROM usuarios JOIN estados.id = usuarios.estado_id
ORDER BY CASE estados.nome
  WHEN 'Acre' THEN 'Acre'
  WHEN 'Alagoas' THEN 'Alagoas'
  ELSE 'Amazonas'
END ASC