How to Order Rows by Timestamps Prioritized by Specific IDs using CASE Statement in MySQL?

MYSQL ORDER BY CASE Issue: Combining Timestamps for Ordering

In the presented scenario, we have a table with four rows, where each row contains two IDs and two timestamps. The task is to retrieve all four rows and order them based on timestamps in a specific way.

The goal is to prioritize the timestamp_one column for ordering when id_one matches 27, and prioritize the timestamp_two column when id_two matches 27. The expected output is to have the rows ordered as if the two timestamp columns were one.

To achieve this, we can modify the existing statement 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

In this updated statement, we prioritize ordering by timestamp_one when id_one is 27 and timestamp_two when id_two is 27. The CASE statement ensures that the correct timestamp column is used for each row.

The resulting output will be ordered as follows:

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

This meets the requirement of ordering the rows as if the two timestamp columns were one, prioritizing timestamp_one for rows with id_one of 27 and timestamp_two for rows with id_two of 27.

The above is the detailed content of How to Order Rows by Timestamps Prioritized by Specific IDs using CASE Statement in MySQL?. For more information, please follow other related articles on the PHP Chinese website!