Mysql sorting by specified value Return all values

Asked

Viewed 227 times

1

Good afternoon, Is there any method in an sql query to sort from a value and if there is this value return it from first and then all the rest of the values ?

Example : A table with days from 1 to 7, where I would put in case it existed return the value 5 first and then all others ( "123467")

Would almost be a Where but that I could pull the other values also after this selected value.

        $dia =  5;

        $query = $this->db->query("SELECT * FROM tabela where dia = $dia");

       //Teria que retornar query com o primeiro valor = 5 e depois todos outros valores.
php mysql
  • 1

    Just make a ORDER BY dia != 5, dia, in your case, ORDER BY dia != $dia, dia. Anything that runs away from that, complicates it for nothing, and you still run the risk of not taking advantage of the ratings. The explanation is in the original post linked above (attention to the !=, which is false to be sorted as less than true). If you want, make a FIDDLE SQL with some sample data, which I demonstrate the query to you.

  • By the way, I had already made a fiddle in 2016, I didn’t even remember (it’s in the original post). Test here: http://sqlfiddle.com/#! 9/52a4c3/1 - The difference of the example there is that as the second criterion we are using another field.

  • 1

    Thank you @Bacco

3 answers

1


In order for the record to have priority over other values and to be first can use Mysql FIELD Function.

SELECT * FROM tabela ORDER BY FIELD(dia, $dia) DESC, dia ASC"

We defined the $dia with priority or more relevance through instruction ORDER BY FIELD(dia, $dia) DESC

Source

Another way: Mysql CASE Function.

select * from tabela order by case when dia = $dia then 1 else 2 end, dia

Especially in Mysql, you can also do

select * from tabela order by dia <> $dia,  dia

Since the result of a comparison in Mysql is 0 or 1 you can sort by this result. Not Equal

-1

Make two queries and use the results in sequence:

SELECT * FROM tabela where dia = $dia"
SELECT * FROM tabela where dia <> $dia"

"Ah, but I don’t want to make two appointments..."

Then unite the results of the two queries into one, order by the field you want and make the query about this result.

SELECT resultado.dia, ordem -- outros campos
  FROM (
       SELECT dia, 1 as ordem -- outros campos
         FROM tabela where dia = 5
       UNION
       SELECT dia, concat(2, dia) as ordem -- outros campos
         FROM tabela where dia <> 5 
     ORDER BY dia
  ) as resultado
ORDER BY ordem
  • 1

    Reminding the glorious downvoter that this is the only answer that does not use specific Mysql functions, that is, if someone comes to this question with the same problem (including the author himself) and wants to do the same thing, only in another BD, the solution with SQL standard is the only one that works.

-1

You can do as follows, think I have a table with the name teste and its fields id and nome, you can use the ORDER BY FIELD Mysql and your query would look like this:

SELECT
   id,
   nome
FROM teste
ORDER BY FIELD(id, 3) DESC;

where the result would be:

inserir a descrição da imagem aqui

I think that’s what you need.

Hug

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.