Set a condition in order by mysql

Question

I am having doubts about a subject related to order by. In my table you have the following information:

ID | ORDEM | NOME
1  |  0    | NOME01
2  |  2    | NOME02
3  |  1    | NOME03

I want to give a select where you will first sort by the ORDER column, then by the ID column, but if in the ORDER column you have 0 then it will be last As an example below:

ID | ORDEM | NOME
3  | 1     | NOME03
2  | 2     | NOME02
1  | 0     | NOME01

The code I thought so far was

SELECT ID, ORDEM, NOME FROM agenda ORDER BY ordem ASC, ID ASC

But what’s in the order of zero comes up first. How can I see this.

Answer 1

If I could understand what you meant it would be this,

 SELECT ID, ORDEM, NOME 
    FROM agenda
     ORDER BY   (case when ordem = 0 then ordem end) desc,
                 (case when ordem <> 0 then ordem end), ID ASC;