基于Timestamp筛选SHIPMENT_DATA，排除最新成功及指定消息记录的SQL改写

Alright, let's fix your SQL query to meet those requirements. Here's a step-by-step breakdown and the revised code:

Requirements Recap #

We need to:

• Exclude entire shipments where the most recent record has a status_code of 1000 (like shipment 12456 in your sample data)
• Filter out any records where the message field contains a specific piece of text
• Keep all valid records from shipments that don't meet the exclusion rules

Revised SQL Query #

SELECT createdDate, status_code, message
FROM SHIPMENT_DATA
WHERE 
    -- Keep only shipments where the latest status is NOT 1000
    shipmentid IN (
        SELECT shipmentid
        FROM (
            SELECT 
                shipmentid,
                status_code,
                -- Assign row number ordered by newest first per shipment
                ROW_NUMBER() OVER (PARTITION BY shipmentid ORDER BY createdDate DESC) AS rn
            FROM SHIPMENT_DATA
        ) latest_status
        WHERE rn = 1 AND status_code != 1000
    )
    -- Exclude records with the specified text in message
    AND message NOT LIKE '%your-specified-text%'
    -- Optional: Keep your original shipment ID filter if needed
    -- AND shipmentid IN ('12456', '222', '333')

How It Works #

1. Inner Subquery (latest_status):
   • Uses ROW_NUMBER() to assign a sequential number to each record in a shipment, ordered by createdDate descending. The newest record for each shipment gets rn = 1.
2. Middle Filter:
   • Grabs only the shipmentids where the latest record (rn = 1) has a status_code not equal to 1000. This excludes any shipment where the most recent status is success.
3. Outer Query:
   • Pulls all records from those valid shipments, and adds the filter to exclude any records where message contains your specified text (replace %your-specified-text% with the actual text you want to block, e.g., %error%).

Example Data Outcome #

Using your sample data:

• Shipment 12456: Latest status is 1000 → entire shipment is excluded
• Shipment 222: Latest status is 1001 → its record is kept (assuming message doesn't contain your specified text)
• Shipment 333: Latest status is 1000 → entire shipment is excluded

Notes #

• If you only want to show the latest valid record for each shipment (not all historical records), you can adjust the query to filter for rn = 1 in the outer layer instead. Let me know if you need that version!
• This works in most modern databases (Oracle, MySQL 8+, SQL Server, PostgreSQL) that support window functions like ROW_NUMBER().

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