Mysql sorting by specific value by returning all values [duplicate]

Navigation
2

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

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

It would be almost a where but I could pull the other values also after that 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.
    
asked by anonymous 16.03.2018 / 20:39

3 answers

1

In order for the record to have priority over the other values and to be first you can use MySQL FIELD function . 

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

We define the $dia with priority or more relevance through the 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 that result. Not equal

    
18.03.2018 / 16:56
-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 do not want to do two queries ..."

Then merge the results of the two queries into one, sort by the field you want and query on that 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
    
16.03.2018 / 21:04
-1

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

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

where the result would be:

I think that's what you need.

Embrace

    
16.03.2018 / 22:56
Variable out of while is zero Ajax does not work when it comes from a select form