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
– Ryan Gadsdon

2017-09-25 13:43:24 +00:00
Commented Sep 25, 2017 at 13:43 — Ryan Gadsdon
– Ryan Gadsdon, Commented Sep 25, 2017 at 13:43

MT0 · Accepted Answer · 2017-09-25 13:47:44Z

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, 2017 at 13:47

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Morgan Forever Over a year ago

it returns: "rn Invalid Identifier)

MT0 Over a year ago

@MorganForever did you include the AS rn part?

Tony Kru · Accepted Answer · 2017-09-25 13:56:00Z

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)

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.

Collectives™ on Stack Overflow

Select MAX Value for Each ROW - Oracle Sql

2 Answers 2

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Related