CODEWITHSUNDEEP
sqloracle-database
butter
butter

Reputation: 63

select max(date) for each group SQL

I have a table like this :

QUOTE_ID   CREATED_DATE  CHARGE_1 CHARGE_2
2    2018-06-11 19:51:10.0    00   16
2    2017-06-11 19:51:10.0    01   16
3    2017-06-11 19:51:10.0    10   16
4    2019-06-11 19:51:10.0    20   16
4    2020-06-11 19:51:10.0    02   16
4    2017-06-11 19:51:10.0    00   16

i would like to select only per each QUOTE_ID the row which has the MAXimum CREATED_DATE.

So the result should be like this :

QUOTE_ID   CREATED_DATE  CHARGE_1 CHARGE_2
2    2018-06-11 19:51:10.0    00   16
3    2017-06-11 19:51:10.0    10   16
4    2020-06-11 19:51:10.0    02   16

I tryed with this SQL code (with ORACLE )

SELECT tt.*
FROM CUSTOM_LIFETIME_VALUE_LOY_JOIN tt
INNER JOIN
    (SELECT "QUOTE_ID", MAX("CREATED_DATE") AS MaxDateTime
    FROM CUSTOM_LIFETIME_VALUE_LOY_JOIN
    GROUP BY QUOTE_ID) groupedtt 
ON tt.QUOTE_ID = groupedtt.QUOTE_ID 
AND tt.CREATED_DATE = groupedtt.MaxDateTime

But it seems no correct since i didnt get all the QUOTE_ID rows

Any idea please to help me to resolve this problem?

Thanks

Upvotes: 1

Views: 308

Answers (2)

Imran
Imran

Reputation: 169

Try this SQL code (with ORACLE )

SELECT tt.*
FROM CUSTOM_LIFETIME_VALUE_LOY_JOIN tt
WHERE (tt.QUOTE_ID,tt.CREATED_DATE) IN 
      (SELECT QUOTE_ID, MAX("CREATED_DATE") AS MaxDateTime
     FROM CUSTOM_LIFETIME_VALUE_LOY_JOIN
     GROUP BY QUOTE_ID)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

The canonical way is to use row_number():

select clv.*
from (select clv.*,
             row_number() over (partition by quote_id order by created_date desc) as seqnum
      from CUSTOM_LIFETIME_VALUE_LOY_JOIN clv
     ) clv
where seqnum = 1;

Note that your version should work as well -- except when quote_id is NULL.

Upvotes: 2

Related Questions