SQL从max（日期）中选择一行

I have been trying to get some specific but could not get it.

我一直试图得到一些具体但无法得到它。

SELECT 
    idStyle, 
    idStyleDtl, 
    costPrice, 
    MAX(createDt) created, 
    idCustomer 
FROM INVOICE_DTL
GROUP BY 
    idStyle, idStyleDtl,costPrice, idCustomer
ORDER BY 
    idStyle, idCustomer

This is my query and

这是我的查询和

This is the result. What I expected though the highlighted part would be merged and show only one row with created:2017-02-01 12:38:31.597 and costPrice:11.50(because it's the same row as MAX(createDt)) since they have same idStyle, idStyleDtl, and idCustomer. I thought MAX(costPrice) might be it but it just returned higher costPrice not the one in the same row as MAX(createDt).

结果就是这样。我所期望的虽然突出显示的部分将被合并,并且只显示创建的一行:2017-02-01 12:38:31.597和costPrice:11.50(因为它与MAX(createDt)的行相同),因为它们具有相同的idStyle, idStyleDtl和idCustomer。我认为MAX(costPrice)可能是它,但它只返回了更高的costPrice而不是与MAX(createDt)在同一行中的那个。

I want to get one entire row having MAX(createDt)result when there's same idStyle, idStyleDtl, and idCustomer.

当有相同的idStyle,idStyleDtl和idCustomer时,我希望得到一个具有MAX(createDt)结果的整行。

Even small advice will be appreciated. Thanks!

即使是小建议也会受到赞赏。谢谢!

2 个解决方案

#1

You can try to use ROW_NUMBER with window function to make the row number by idStyle, idStyleDtl, idCustomer columns and order by createDt DESC in subquery.

您可以尝试使用带窗口函数的ROW_NUMBER来通过idStyle,idStyleDtl,idCustomer列生成行号,并通过子查询中的createDt DESC进行排序。

then get rn = 1 data, which mean the max date.

然后得到rn = 1数据,这意味着最大日期。

SELECT * FROM (
    SELECT 
        idStyle, 
        idStyleDtl, 
        costPrice, 
        createDt, 
        idCustomer,
        ROW_NUMBER() OVER(PARTITION BY idStyle, idStyleDtl, idCustomer ORDER BY createDt DESC) rn
    FROM INVOICE_DTL
) t1
where rn = 1
ORDER BY 
    idStyle, idCustomer

#2

Often, the most efficient method is a correlated subquery:

通常,最有效的方法是相关子查询:

select i.* 
from invoice_dtl i
where i.created_dt = (select max(i2.created_dt)
                      from invoice_dtl i2
                      where i2.idCustomer = i.idCustomer and 
                            i2.idStyle = i.idStyle and
                            i2.idStyleDtl = i.idStyleDtl
                     )
order by i.idStyle, i.idCustomer;

In particular, this can take advantage of an index on (idCustomer, idStyle, idStyleDtl, created_dt).

特别是,这可以利用索引(idCustomer,idStyle,idStyleDtl,created_dt)。

#1