CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
Morgan Forever
Morgan Forever

Reputation: 117

Select MAX Value for Each ROW - Oracle Sql

I have one doubt. I need to find what is the latest occurrence for a specific list of Customers, let's say to simplify, I need it for 3 Customers out of 100. I need to check when it was the last time each of them got a bonus. The table would be:

EVENT_TBL

Fields: Account ID, EVENT_DATE, BONUS ID, ....

Can you suggest a way to grab the latest (MAX) EVENT DATE (that means one row each)

I'm using SELECT...IN to specify the Account ID but not sure how to use MAX, Group BY etc etc (if ever needed).

Upvotes: 1

Views: 165

Answers (2)

Tony Kru
Tony Kru

Reputation: 41

You can try

with AccountID_Max_EVENT_DATE as (
 select AccountID, max(EVENT_DATE) MAX_D
 from EVENT_TBL 
 group by AccountID
)
SELECT E.* 
FROM EVENT_TBL E
INNER JOIN AccountID_Max_EVENT_DATE M 
ON (E.AccountID = M.AccountID AND M.MAX_D = E.EVENT_DATE)

Upvotes: 0

MT0
MT0

Reputation: 167774

Use the ROW_NUMBER() analytic function:

SELECT *
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( PARTITION BY Account_id ORDER BY event_date DESC ) AS rn
  FROM   EVENT_TBL t
  WHERE  Account_ID IN ( 123, 456, 789 )
)
WHERE  rn = 1

Upvotes: 4

Related Questions