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

Navigation
8

Using as an example, I have a certain table of users, where the city of that user is, is a reference to another table.

I need the order of these users in a query to be given according to the location of the current logged-in user. For example, if I live in Belo Horizonte , the user data of Belo Horizonte must first be displayed in this query.

I know that in MySQL ORDER BY works as a sort order by the present value in the column, but I do not know how I can sort by specific values.

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

In a fictitious example I'll show you what I want:

First I get the value of the city: 

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

Then I want the ordering of records to begin 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>

Code fiction - I know it would give syntax error, it's just the demonstration of what I want.

Is there any way to sort the data of a query, not by the table field, but by specified default values?

    
asked by anonymous 14.09.2015 / 21:53

4 answers

10

It would look something like this: 

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 .

    
14.09.2015 / 22:04
6

Yes, you can do something like this: 

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 very simple, if the user state is indicated in the $ value variable, then we assign the value 1 (indicating a higher priority), otherwise we assign 2. After that you can use any other column to sort the remaining registers . In this case I order in alphabetical order of states.

Here's SQLFiddle to see how it works in practice.

    
14.09.2015 / 22:00
5

Yes, you can literally specify the values that 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
    
14.09.2015 / 22:05
4

It would be like this, if you have Acre and Alagoas, you will order them, if you do not have either, you 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
    
14.09.2015 / 22:10
Search via ajax during typing in input text (autocomplete) Function that lists letters of the alphabet