MYSQL ORDER BY CASE Issue

mysql

Solution

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

Problem

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.

Original source