CODEWITHSUNDEEP
mysqlif-statement
mr_app
mr_app

Reputation: 1312

MySQL Order by 2 columns with if statement

I have a problem with ordering a search result by 2 colums.

I have a table in following format

id  pos date
------------------
65  0   2012-08-10
66  0   2012-08-09
70  0   2012-08-08
73  0   2012-08-07
74  0   2012-08-06
75  0   2012-08-05
76  1   2012-08-04
77  2   2012-08-03
78  0   2012-08-02
79  0   2012-08-01

My problem is, that the system (cms) produces (int)0's for NULL . SO i get not the result, i need.

I want to order by pos(if not (int)0 and than date), so that pos only forces the position, when it is >=1 My question. Is there a way to use an IF statement in the ORDER clause?

Upvotes: 0

Views: 1764

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

To sort according to pos if and only if it's set, and leave all zeroes for last ordered by date; the 4E9 is just a number high enough to be larger than all the values in pos.

SELECT * FROM MyTable 
ORDER BY CASE pos WHEN 0 THEN 4E9 ELSE pos END, `date`;

Demo at SQLfiddle.

Another a bit simpler mysql-specific version that gets rid of the "magic constant" is this;

SELECT * FROM MyTable 
ORDER BY pos=0, pos, `date`;

Another SQLfiddle.

Upvotes: 4

Related Questions