Select MAX Value for Each ROW - Oracle Sql

Question

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

example code and desired results? – Ryan Gadsdon Sep 25 '17 at 13:43 — Ryan Gadsdon, Sep 25 '17 at 13:43

score 4 · Accepted Answer · answered Sep 25 '17 at 13:47

4

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

answered Sep 25 '17 at 13:47

MT0

143,790
11
59
117

it returns: "rn Invalid Identifier) – Morgan Forever Sep 25 '17 at 14:26
1

@MorganForever did you include the `AS rn` part? – MT0 Sep 25 '17 at 14:28

score 0 · Answer 2 · answered Sep 25 '17 at 13:56

0

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)

answered Sep 25 '17 at 13:56

Tony Kru

41
5

This uses a self-join and may require two table scans. Also, if there are multiple entries with the same (maximum) date then this will return more than one row per account. – MT0 Sep 25 '17 at 14:05

Select MAX Value for Each ROW - Oracle Sql

2 Answers2