MySQL: order by two columns with NULLs last?

Question

I know there are similiar questions on SO, but unfortunately I haven't found the answer to mine, so asking here.

My result table structure looks like this:

id    item_id   price    group
-------------------------------
1     3472      NULL      
2     234       300      1
3     4872      150      1
4     1109      100
5     5582      300      0
6     144       150      2
7     1384      NULL     1
8     627       100      2

price column can have NULLs and group column can have empty results.

The table is sortable by price.

What I need is:

1. Order the result set by two columns - price and group

2. Results with the same price have to be ordererd according to their groups first (ascending), results with the same price but without group go after them.

3. NULLs always have to be last regardless of price sort order (DESC or ASC).

So, the table with ordering should look like this:

id    item_id   price    group
-------------------------------
8     627       100      2
4     1109      100
3     4872      150      1
6     144       150      2
5     5582      300      0
2     234       300      1
7     1384      NULL     1
1     3472      NULL      

When I need to order with NULLs last I use something like:

ORDER BY ISNULL(column), column

And ordering by multiple columns usually is not a problem, but I just stuck with this one. I've spent a few hours with no results.

Answer 1

ORDER BY IF( ISNULL( price ), 1, 0 ), price, IF( group='', 1, 0 ), group ASC