Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MYSQL ORDER BY CASE Issue

Tags:

mysql

I have a database like this:

------------------------------------------------------------------- |   id_one   |   id_two   |   timestamp_one   |   timestamp_two   | ------------------------------------------------------------------- |     27     |     35     |        9:30       |        NULL       | ------------------------------------------------------------------- |     35     |     27     |        NULL       |        9:35       | ------------------------------------------------------------------- |     27     |     35     |        9:34       |        NULL       | ------------------------------------------------------------------- |     35     |     27     |        NULL       |        9:33       | ------------------------------------------------------------------- 

I need pull all 4 rows

ORDER BY 'timestamp_one' if 'id_one'=27 or  ORDER BY 'timestamp_two' if 'id_one'=27 

This is the statement I have now:

SELECT * FROM tablename  WHERE id_one=27 OR id_two=27  ORDER BY    CASE WHEN id_one=27 THEN timestamp_one END DESC,    CASE WHEN id_two=27 THEN timestamp_two END DESC 

This works good in that is outputs this:

------------------------------------------------------------------- |   id_one   |   id_two   |   timestamp_one   |   timestamp_two   | ------------------------------------------------------------------- |     27     |     35     |        9:30       |        NULL       | ------------------------------------------------------------------- |     27     |     35     |        9:34       |        NULL       | ------------------------------------------------------------------- |     35     |     27     |        NULL       |        9:33       | ------------------------------------------------------------------- |     35     |     27     |        NULL       |        9:35       | ------------------------------------------------------------------- 

But I need to two timestamp columns to order like they are one so it would order like this:

------------------------------------------------------------------- |   id_one   |   id_two   |   timestamp_one   |   timestamp_two   | ------------------------------------------------------------------- |     27     |     35     |        9:30       |        NULL       | ------------------------------------------------------------------- |     35     |     27     |        NULL       |        9:33       | ------------------------------------------------------------------- |     27     |     35     |        9:34       |        NULL       | ------------------------------------------------------------------- |     35     |     27     |        NULL       |        9:35       | ------------------------------------------------------------------- 

I hope this makes sense. Essentially, I am trying to have two ORDER BY columns that are specific to a WHERE condition. Then once the correct ORDER BY column is chosen for that row, it orders the ROWS by the timestamp as a whole.

like image 764
ATLChris Avatar asked Dec 03 '10 15:12

ATLChris


1 Answers

SELECT id_one, id_two, timestamp_one, timestamp_two       FROM tablename  WHERE id_one = 27      OR id_two = 27  ORDER BY      CASE          WHEN id_one=27 THEN timestamp_one          WHEN id_two=27 THEN timestamp_two      END DESC  
like image 83
D'Arcy Rittich Avatar answered Sep 25 '22 21:09

D'Arcy Rittich