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!

Reputation: 750

Multiple ORDER BY

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:

Is there any possibility to do this ordering?

Thank you all!

Upvotes: 3

Reputation: 1269503

Unfortunately, MySQL doesn't support NULLS LAST/NULLS FIRST. However, it is easily incorporated into the query:

order by top1 desc, top2 desc, top3 desc,
         (ordercol is null or ordercol = 0) asc  -- put these values last

Upvotes: 0

Reputation: 222402

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.

Upvotes: 2