MySQL按两个日期字段取较大值排序的正确SQL语句咨询

Problem Statement #

I'm working on a MySQL query that needs to sort records based on specific rules involving date_created and date_updated fields. Here's the sample data:

Sorting Rules #

1. If date_updated is null, use date_created for sorting.
2. If date_updated > date_created (the normal case), use date_updated for sorting.
3. If two records have the same sorting value (one using date_created because date_updated is null, the other using date_updated), the record with null date_updated should come first.

Expected Result #

I tried this SQL query, but it doesn't meet rule 2:

SELECT * FROM table t ORDER BY CASE WHEN t.date_updated IS NOT NULL AND t.date_updated > t.date_created THEN t.date_created ELSE t.date_updated END DESC

What's the correct MySQL query to satisfy all three rules?

Solution #

Got it, let's fix this sorting issue step by step. First, the problem with your original query is that your CASE statement had it backwards: when date_updated is valid and newer than date_created, you were using date_created for sorting instead of date_updated—that's why rule 2 wasn't being met.

To cover all three rules, we need two parts in our ORDER BY clause:

1. First, select the correct date to sort by (following rules 1 and 2) and sort in descending order.
2. Second, add a priority flag to ensure records with null date_updated come first when their sorting dates match (rule 3).

Here's the corrected query:

SELECT * 
FROM `table` t 
ORDER BY 
    -- Rule 1 & 2: Pick the right date for sorting
    CASE 
        WHEN t.date_updated IS NOT NULL AND t.date_updated > t.date_created THEN t.date_updated
        ELSE t.date_created 
    END DESC,
    -- Rule 3: Prioritize records with null date_updated when sorting dates are equal
    CASE WHEN t.date_updated IS NULL THEN 0 ELSE 1 END ASC;

How This Works #

• First Sort Field: The CASE now correctly uses date_updated for the normal scenario (rule 2). For null date_updated or cases where date_updated isn't newer than date_created, it falls back to date_created (rule 1). We sort this field descending to get the newest records at the top.
• Second Sort Field: This CASE assigns a lower value (0) to records with null date_updated and 1 to others. Sorting this ascending ensures that when two records have the same sorting date, the one with a null date_updated takes precedence (rule 3).

Running this query against your sample data will produce exactly the expected result you listed.

内容的提问来源于stack exchange，提问作者Lawrence Colombo