CODEWITHSUNDEEP
mysql
Anders
Anders

Reputation: 279

Order by value, but disregard zero

Can you help me out here?

I'm doing a simple SQL-query for a calendar, which lists a number of values (date, time etc as UNIX-time), according to their values and limited to 5 entries.

The relevant part of the querystring is this:

ORDER BY specialtime DESC, `start` ASC, programstart ASC

The column named "specialtime" lists these values:

432000
259200
172800
0
0
0
0

That's almost ok - but I would like entries with a value, different from zero to appear in ascending order - and after them, the rest, also ascending... like this:

172800
259200
432000
0
0
0
0

Is there a way to make this happen in SQL without relying on post-processing the values?

Upvotes: 2

Views: 3314

Answers (1)

GolezTrol
GolezTrol

Reputation: 116140

Check if special time is 0 and sort by that expression primarily:

ORDER BY specialtime = 0, specialtime ASC, `start` ASC, programstart ASC

MySQL treats boolean expressions as integers, so false evaluates to 0 and true evaluates to 1. You can sort by such an expression and every row where specialtime = 0 evaluates to true is moved to the bottom of the list.

Or, slightly longer, but also more portable and perhaps easier to understand:

ORDER BY CASE WHEN specialtime = 0 THEN 1 ELSE 0 END, specialtime ASC, `start` ASC, programstart ASC

The CASE expression returns 0 (of specialtime is not 0) or 1 (if specialtime is 0). The query is sorted primarily on this value, so all the 0 times are at the bottom of the list. So basically the functionality is the same, but the version with case is more portable, since not many databases allow sorting by a boolean expression.

The value of specialtime is used for secondary sort, so withing that group when specialtime = 0 returns false, the records are still sorted on the actual value of specialtime.

Upvotes: 9

Related Questions