获取指定列最大值对应的行——SQL查询调整需求

Got it, let's work through this. You want to tweak your existing SQL query to pull the full row that corresponds to the maximum value of a specific column (I’m guessing you mean the latest cost_call_date per group, since that’s a date field in your query—if it’s a different column, just swap it in!). Here are two solid approaches:

The ROW_NUMBER() window function lets you rank rows within groups, so you can easily pick the top row (the one with your maximum value) for each group. Let's assume you want to group by acc_id (adjust to project_id if that's your actual grouping need) and grab the row with the newest cost_call_date:

SELECT *
FROM (
    SELECT 
        a.project_id, 
        a.acc_name, 
        a.project_name, 
        a.iot,
        a.ilc_code, 
        a.active, 
        a.license_no, 
        c.line_id, 
        c.chargable_fte, 
        c.cost_call_date,
        -- Rank rows in each acc_id group by cost_call_date (newest first)
        ROW_NUMBER() OVER (PARTITION BY a.acc_id ORDER BY c.cost_call_date DESC) AS row_rank
    FROM Account a 
    INNER JOIN account_version c USING (acc_id) 
    WHERE 
        a.acc_name = 'APMM' 
        AND EXTRACT(MONTH FROM c.cost_call_date) BETWEEN 1 AND 4
        AND EXTRACT(YEAR FROM c.cost_call_date) = 2018
) ranked_data
WHERE row_rank = 1 -- Grab only the top-ranked (max date) row per group
ORDER BY line_id DESC;

Quick Notes: #

• Swap PARTITION BY a.acc_id with PARTITION BY a.project_id if you need to group by project instead of account.
• If multiple rows in a group have the exact same maximum cost_call_date, ROW_NUMBER() will assign unique ranks—so you’ll only get one row. If you want all matching rows, use RANK() instead.

If your database doesn’t support window functions, you can first find the maximum value per group with a subquery, then join back to your tables to get the full row:

SELECT 
    a.project_id, 
    a.acc_name, 
    a.project_name, 
    a.iot,
    a.ilc_code, 
    a.active, 
    a.license_no, 
    c.line_id, 
    c.chargable_fte, 
    c.cost_call_date
FROM Account a 
INNER JOIN account_version c USING (acc_id)
INNER JOIN (
    -- Get the latest cost_call_date for each acc_id in your date range
    SELECT acc_id, MAX(cost_call_date) AS latest_cost_date
    FROM account_version
    WHERE 
        EXTRACT(MONTH FROM cost_call_date) BETWEEN 1 AND 4
        AND EXTRACT(YEAR FROM cost_call_date) = 2018
    GROUP BY acc_id
) max_dates 
    ON c.acc_id = max_dates.acc_id 
    AND c.cost_call_date = max_dates.latest_cost_date
WHERE 
    a.acc_name = 'APMM' 
ORDER BY c.line_id DESC;

Heads Up: #

• This method will return all rows in a group that share the exact maximum cost_call_date (if there are duplicates). If you only want one, stick with the window function approach.

I also cleaned up your original date filters to use BETWEEN and direct equality instead of redundant >=/<= checks—same functionality, cleaner code.

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