How to Sort Rows Based on a Conditional Timestamp in MySQL?

MySQL ORDER BY CASE Sorting Issue

In MySQL, the ORDER BY clause allows you to sort query results based on specified columns. However, you may encounter difficulties when sorting multiple columns that depend on a specific condition.

Consider the following scenario:

You have a table with two columns, timestamp_one and timestamp_two. You need to retrieve all rows where either id_one or id_two is equal to 27. Additionally, you want to sort the results based on either timestamp_one or timestamp_two depending on whether id_one or id_two is 27.

While the following query correctly retrieves the required rows:

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

It sorts the rows by each column individually, rather than combining them into a single timestamp for sorting.

To resolve this issue, you can use the following 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 modified query, we simplify the ORDER BY clause by using a single CASE expression to determine the sorting order. This allows us to sort the rows based on a combined timestamp that dynamically selects the appropriate column (timestamp_one or timestamp_two) depending on the value of id_one and id_two.

The above is the detailed content of How to Sort Rows Based on a Conditional Timestamp in MySQL?. For more information, please follow other related articles on the PHP Chinese website!