选择子组中的最新行 [英] Selecting latest rows in subgroups

问题描述

我有由连接和一些条件创建的下表:

I have the following table created by a join and some conditionals:

product_id     date
11111          2012-06-05
11111          2012-05-01
22222          2011-05-01
22222          2011-07-02
33333          2011-01-01

我正在尝试获取行，以便获得每个产品的最新日期的结果集:

I am trying to get the rows such that I have a result set with the latest date per product:

GOAL

product_id     date
11111          2012-06-05
22222          2011-07-02
33333          2011-01-01  

我可以按原样提取数据并进行手动排序，但我不想这样做.我似乎无法找到一种方法来执行 SELECT MAX() 而不只返回一行，我宁愿不对每个产品 ID 运行查询.

I could extract the data as is and do a manual sort, but I'd rather not. I cannot seem to find a way to do a SELECT MAX() without returning only a single row, and I'd rather not run a query for each product id.

该表由此查询生成:

SELECT item_id, sales_price, item, description, transaction_date 
FROM db.invoice_line AS t1 INNER JOIN db.invoices AS t2 
ON t1.invoice_id = t2.id_invoices WHERE item IS NOT NULL 
AND item_id != '800001E9-1325703142' AND item_id != '800002C3-1326830147' 
AND invoice_id IN 
    (SELECT id_invoices FROM db.invoices 
       WHERE customer_id = '[variable customer id]' 
       AND transaction_date >= DATE_SUB(NOW(), INTERVAL 360 DAY));

我使用连接来添加"日期列.之后，我会忽略无用的项目，并从一年前至今的特定客户的发票中进行选择.

I use a join to 'add' the date column. After that, I disregard useless items, and select from invoices from a particular customer from a year ago to date.

感谢您的指导.

丹麦人

推荐答案

看起来 group by 很适合:

select  product_id
,       max(date)
from    YourTable
group by
        product_id

这篇关于选择子组中的最新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！