How to Order by the Earlier Timestamp from Two Columns Using MySQL CASE Statement?

MYSQL ORDER BY CASE Issue

In MySQL, using the CASE statement within the ORDER BY clause allows you to specify multiple sorting criteria based on particular conditions. However, challenges arise when attempting to sort two different timestamp columns as if they were a single, unified timestamp.

Consider the provided table structure:

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

The goal is to order the rows by the earlier timestamp, regardless of whether it is stored in the timestamp_one or timestamp_two column. The following query attempts to achieve this using the CASE statement:

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

While this query successfully orders the rows correctly for id_one=27, it fails to combine the timestamps into a single sorting criterion.

To resolve this issue, the query can be modified as follows:

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 

By using a single CASE expression instead of separate CASE statements for each condition, MySQL merges the timestamps into a single sorting criterion. This ensures that the rows are ordered correctly, as desired:

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

The above is the detailed content of How to Order by the Earlier Timestamp from Two Columns Using MySQL CASE Statement?. For more information, please follow other related articles on the PHP Chinese website!