Case when SQL priority-CodePudding

I'm trying to make a query in which the records that have the priority column > 0 can be sorted by ASC priority, and the records that the priority column is 0, sorted by id DESC.

The way I did it works, but the DESC id ordering is being listed first than the priority order. Thus, the records with priority 0 are first. How to make the records with priority first?

see the code

select * from registros ORDER BY CASE WHEN prioridade > 0 THEN prioridade END ASC, id DESC

CodePudding user response：

You may use a two level sort here:

SELECT *
FROM registros
ORDER BY
    CASE WHEN prioridade > 0 THEN 0 ELSE 1 END,
    CASE WHEN prioridade > 0 THEN prioridade ELSE -1*prioridade END;

CodePudding user response：

Assuming you want something like this -

Priority	Id
1	5
2	4
3	1
0	3
0	2

You need to change your case statement so that records with priority 0 will be guaranteed to be last when sorted by ascending priority. The easiest way to do that is to hard code the sort value for priority 0 to some value higher than the highest priority (such as 999)

SELECT * 
FROM registros  
ORDER BY  
    CASE WHEN prioridade = 0 THEN 999 ELSE prioridade END ASC,
    id DESC