How to make SELECT with ORDER BY and different criteria?

Question

People can select with 2 "ORDER BY"?

Table CATEGORY:

If I make a SELECT like this:

select id,nome from CATEGORIA ORDER BY nome ASC

Mysql returns the names in alphabetical order, but I need it to always return the id:100 first and display the others in alphabetical order by column Name.

You can do this in a single SELECT?

Answer 1

The ORDER BY allows you to specify various criteria:

SELECT ... ORDER BY expressao1, expressao2, expressao3...

In your case, that’s enough:

SELECT id, nome FROM categoria ORDER BY id != 100, nome

See working perfectly on SQL Fiddle.

• The expression id != 100 will return false, which is ordained before true, leaving the chosen record always first. As a tiebreaker, it will be used nome.

• You can use several conditions then in the same ORDER when you need more than one criterion, just sort from the most important to the least important, separating by comma.

  Example: ORDER BY idade, nome, pontos will order by idade, but when the age "stalls", will order by nome, and only in the case of idade and nome be equal is that the ordination will be pontos.

• The keyword ASC is unnecessary as ascending ordering is the standard of the bank, but nothing prevents you from maintaining it. Of curiosity, could use ORDER BY id = 100 DESC, nome ASC that would equal.

Answer 2

Use Case When. In my example only the registration with id 100 will be 1, then will be returned first.

The other records always returned 2 which will occur a draw, which is worth the second criterion in the Order by.

Table creation:

CREATE TABLE [dbo].[testes](
    [id] [int] NULL,
    [nome] [varchar](50) NULL
) ON [PRIMARY]

insert into testes(id,nome)
values(100,'ESTOQUE');

insert into testes(id,nome)
values(101,'VENDAS');

insert into testes(id,nome)
values(102,'CONTAS');

Query:

select *
from testes
order by CASE WHEN id = 100 THEN 1 ELSE 2 END, nome

Sqlfiddle

Answer 3

I got the 100 always come first using two queries:

select id, nome from CATEGORIA where id = 100
union 
(select id, nome from CATEGORIA where id != 100 ORDER BY nome ASC)
;

Note that the second query needs to be in parentheses, otherwise the ORDER BY shall order the two consultations.

Answer 4

You already have several answers, this is just one more way to do it:

SELECT id, 
       nome, 
       CASE 
          WHEN id = 100 THEN " "
          ELSE nome
       END AS ordenacao
  FROM categoria
 ORDER BY ordenacao

We created a calculated column to order the SELECT.

When the ID is equal to 100 the calculated column is worth a blank space, when ID is any other value, the column shall be equal to the column NOME.

This way, when we order, the line with the white space will come first.

Answer 5

I believe this works, I tested in another bank and it worked, I do not know if it will work in Mysql.

select tabela.id, tabela.nome
from (select 1 as ordem, id, nome from categoria where id= 100
      union
      select 2 as ordem, id, nome from categoria where id != 100
      order by 1,3) as tabela 

Answer 6

Add a new column in the table called "position". Anyway, the name can be any other you want.

For ID 100, save the "position" column as 1, the others leave as 0 or null.

Then at the time of consulting, just sort by the "position" column in the same order with priority over the other sort rules.

select id,nome from CATEGORIA ORDER BY position DESC, nome ASC;

Be aware that it does not mean that it is the best nor the only solution.

I particularly prefer this way compared to other suggestions made because it makes it more flexible. Think about when you need to prioritize a different ID or when you want to reuse the routine for other things. You will have to change the code manually and do various hacks and adaptations.

Answer 7

place the sign in the sorting variables

select id,nome from CATEGORIA ORDER BY 'position' DESC, 'nome' ASC;