postgresql order by

Question

Hi.

My table has a 'number' column which ranges from 0 to around 1000. I want to select everything from the table while ordering it by number. However, I want the rows with number=0 to appear last. I could do it in two queries:

select * from channel_table where number > 0 order by number;
select * from channel_table where number = 0;

But I'm guessing there is a one-line solution?

Answer 1

As you only have one exceptional case, you can use nullif with the nulls clause. Otherwise you would need to order by various case statements to get more complicated ordering logic.

select * from channel_table 
order by nullif(number, 0) nulls last

Answer 2

Is this to simple?

select * from channel_table order by number desc;