How to Order Rows Based on Multiple Timestamp Columns in MySQL with CASE Statement?

MYSQL ORDER BY CASE Issue

This question poses a challenge in ordering rows based on specific columns depending on a WHERE condition. Consider the provided database with four rows and timestamp columns. The goal is to order the rows such that both timestamp columns are treated as a single ordering criterion.

The query provided initially sorts the timestamps based on the id_one and id_two fields separately:

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 approach results in an output where the rows are sorted within each id_one or id_two group, but not as a combined timestamp ordering.

To achieve the desired ordering, we can use a slightly modified query:

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 

In this revised query, both timestamp_one and timestamp_two are considered for ordering, regardless of which ID column matches the WHERE condition. The CASE statement selects the appropriate timestamp based on the id_one and id_two values.

This modification results in the desired output, where the rows are ordered as a single timestamp column, taking both timestamp_one and timestamp_two into account:

---

| 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 |

The above is the detailed content of How to Order Rows Based on Multiple Timestamp Columns in MySQL with CASE Statement?. For more information, please follow other related articles on the PHP Chinese website!