如何从order_status_history表中获取指定日期范围内各order_id的WAI状态最大ID行？

Got it, let's figure out how to pull those latest WAI status records per order within your target date range. First, let's lay out the table data and desired output clearly, then dive into the solutions.

Sample Table Structure & Data #

Desired Output #

Solution 1: Using Window Functions (Modern SQL) #

This is my go-to method because it's clean and readable. We'll use ROW_NUMBER() to rank WAI records for each order by their id (descending, so the latest is first), then filter to grab only the top-ranked record per order.

WITH ranked_wai_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY id DESC) AS record_rank
    FROM order_status_history
    WHERE 
        status = 'WAI'
        AND date BETWEEN '2018-11-12' AND '2018-11-13' -- Swap this with your actual date range
)
SELECT id, date, status, order_id, updated_by
FROM ranked_wai_records
WHERE record_rank = 1;

Solution 2: Subquery Method (For Older SQL Dialects) #

If you're working with a system that doesn't support CTEs or window functions, this approach works by first finding the largest id for each order's WAI records, then joining back to the original table to get the full row details.

SELECT osh.id, osh.date, osh.status, osh.order_id, osh.updated_by
FROM order_status_history osh
INNER JOIN (
    SELECT order_id, MAX(id) AS latest_wai_id
    FROM order_status_history
    WHERE 
        status = 'WAI'
        AND date BETWEEN '2018-11-12' AND '2018-11-13' -- Update this to your date range
    GROUP BY order_id
) latest_ids ON osh.id = latest_ids.latest_wai_id;

Quick Breakdown #

• Both solutions first narrow down the data to only WAI status records within your target date range.
• The window function method assigns a rank to each WAI record per order—since we sort by id descending, the newest record gets rank 1, which we filter for.
• The subquery method first identifies the highest id (latest record) for each order's WAI entries, then matches that id back to the original table to pull the full row.

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