Multiple ORDER BY

Question

I am totally lost in OrderBy clausules.

I need this result after ordering: ID's: 8, 5, 2, 1, 4, 6, 7, 3

Theese rules must be applicated in this order:

• top3 = 1
• top2 = 1
• top1 = 1
• By order ASC, but order "NULL" or 0 to end of line

Is there any possibility to do this ordering?

Thank you all!

Answer 1

I would translate your requirement as:

order by
    top3 desc,
    top2 desc,
    top1 desc,
    nullif(`order`, 0) nulls last

The last criteria shorts by ascending order, while keeping nulls and 0 values last.

Please note that order is a reserved word in all SQL dialects, so not a good choice for a column name.