How to Order MySQL Rows Based on Multiple Timestamp Columns with Conditional Sorting?

MySQL ORDER BY CASE Statement Issue: Ordering Multiple Timestamp Columns

In MySQL, the ORDER BY clause is commonly used to sort the results of a query based on a specific column. However, when dealing with multiple columns that need to be ordered conditionally, a common challenge arises.

Consider the following scenario: you have a database table with a structure as such:

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

The goal is to retrieve all four rows and order them by both timestamp_one and timestamp_two while considering specific conditions. Specifically, if id_one equals 27, the rows should be ordered by timestamp_one; otherwise, if id_two equals 27, they should be ordered by timestamp_two.

To achieve this, many attempts to use the CASE statement in the ORDER BY clause, such as:

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 approach yields the desired ordering for individual rows, it fails to combine the two timestamp columns into a unified ordering, as they are not considered as a single entity.

Solution:

To order the rows as desired, we can use a modified version of the CASE statement in the ORDER BY clause:

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 

This modification treats both timestamp_one and timestamp_two as a single entity and orders the rows accordingly. The CASE statement assigns a descending order to both timestamp columns based on the conditions specified in the WHERE clause, ensuring that the rows are ordered as a whole.

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