How to do SELECT with ORDER BY and different criteria?

22

How can I make a select with 2 "ORDER BY"?

CATEGORY table:

If I do a SELECT like this:

select id,nome from CATEGORIA ORDER BY nome ASC

MySQL returns the names in alphabetical order, but I need them to always return the id: 100 first and present the others in alphabetical order by column Name .

Can you do this in a single SELECT?

    
asked by anonymous 20.06.2016 / 22:04

6 answers

28

ORDER BY allows you to specify several criteria:

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

In your case, this is 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 sorted before true , leaving the chosen record always first. As a tiebreaker, nome will be used.

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

    Example: ORDER BY idade, nome, pontos will sort by idade , but when age "equals", it will sort by nome , and only if idade and nome are the same is that sorting will be by pontos .

  • The ASC keyword is unnecessary because ascending sort is the default for the database, but nothing prevents you from holding. Of curiosity, could use ORDER BY id = 100 DESC, nome ASC that would give in the same.

21.06.2016 / 03:26
11

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

The other records have always returned 2 which will cause a tie, which becomes the second criterion in Order by .

Creating the Table:

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

    
20.06.2016 / 23:42
4

I managed to get 100 to 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 must be enclosed in parentheses, otherwise ORDER BY will sort the two queries.

    
20.06.2016 / 22:29
3

I believe that this works, I tested it 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 
    
20.06.2016 / 23:07
3

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

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

We've created a calculated column to sort SELECT .

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

So when we sort, the line with the whitespace will come first .

    
21.06.2016 / 03:12
3

Add a new column to the table named "position". Anyway, the name can be anything else you want.

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

So when you look up, just sort by the "position" column in descending order with priority over the other sorting rules.

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

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

I particularly prefer this way compared to other suggestions presented as it makes it more flexible. Think about when you need to assign priority to a different ID or when you want to reuse the routine for other things. You will have to change the code manually and make different adjustments and adaptations.

    
20.06.2016 / 23:34