CODEWITHSUNDEEP
mysql
RSilva
RSilva

Reputation: 6943

MySQL - Order by date ASC and DESC, depending on other field

I have these rows in a table...

id  task_type   task_date
1   1           01/15/2010
2   2           01/18/2010
3   1           01/18/2010
4   2           01/16/2010
6   1           01/16/2010
8   1           01/17/2010
9   2           01/17/2010

and I want to order them in a particular way: when task_type=1 order by ASC and when task_type=2 order by DESC

id  task_type   task_date
1   1           01/15/2010
6   1           01/16/2010
8   1           01/17/2010
3   1           01/18/2010
2   2           01/18/2010
9   2           01/17/2010
4   2           01/16/2010

Is this even possible? I've been looking for conditional queries and all i can get is a syntax error...

This is the query that i cant make it work properly:

SELECT id, task_type, task_date 
From Tasks
ORDER BY CASE WHEN task_type=1  THEN
task_type ASC, task_date ASC, hour ASC, priority DESC, id ASC 
WHEN task_type=2 THEN
task_type ASC, task_date DESC, hour ASC, priority DESC, id ASC 
END

Upvotes: 0

Views: 124

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I'm not sure what estado is, but your logic is seems to be:

ORDER BY task_type,
         (CASE WHEN task_type = 1 THEN task_date END) ASC,
         (CASE WHEN task_type = 1 THEN hour END) ASC,
         (CASE WHEN task_type = 1 THEN priority END) DESC,
         (CASE WHEN task_type = 1 THEN id END) ASC,
         (CASE WHEN task_type = 2 THEN task_date END) DESC,
         (CASE WHEN task_type = 2 THEN hour END) ASC,
         (CASE WHEN task_type = 2 THEN priority END) DESC,
         (CASE WHEN task_type = 2 THEN id END) ASC

This can be simplified to:

ORDER BY task_type,
         (CASE WHEN task_type = 1 THEN task_date END) ASC,
         (CASE WHEN task_type = 2 THEN task_date END) DESC,
         hour ASC, priority DESC, id ASC

Upvotes: 1

Related Questions