CODEWITHSUNDEEP
sqlsql-serverdatabase
Min Lee
Min Lee

Reputation: 377

SQL select one row from max(date)

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

Result

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).

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

Even small advice will be appreciated. Thanks!

Upvotes: 0

Views: 1245

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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).

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

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.

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

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

Upvotes: 2

Related Questions