Retrieve max date for distinct IDs in a table [duplicate]

Question

I have the table ABC with the following data

Id Name Date Execution id -- ---- --------- ------------- 1 AA 09SEP2019 11 1 AA 08SEP2019 22 1 AA 07SEP2019 33 2 BB 09SEP2019 44 2 BB 08SEP2019 55 2 BB 07SEP2019 66

And I want to get for every distinct ID in the table its max date. So the result set must be as the following

Id Name Date Execution id -- ---- --------- ------------- 1 AA 09SEP2019 11 2 BB 09SEP2019 44

The query that returns the result I need

WITH MaxDate as ( SELECT Id,Name,Max(Date) from ABC group by Id,Name ) SELECT view1.*, view2.exection_id from MaxDate view1, ABC view2 WHERE view1.date=view2.date and view1.name=view2.name;

I don't like to get the max date for the distinct ID by this way. May be there is another way ? Might be there is more easiest way?

Lukasz Szozda · Accepted Answer · 2019-09-09 16:37:24Z

3

One way is to use RANK:

WITH cte AS ( SELECT ABC.*, RANK() OVER(PARTITION BY Id,Name ORDER BY Date DESC) rnk FROM ABC ) SELECT * FROM cte WHERE rnk = 1 ORDER BY id;

answered Sep 9, 2019 at 16:37

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

1 Comment

Vic VKh Over a year ago

Thank you. Looks like it works for me.

Alex Poole · Accepted Answer · 2019-09-09 16:41:32Z

You can use keep dense_rank last do to this in one level of query, as long as you only want one or a small number of column retained:

select id, name, max(date_) as date_, max(execution_id) keep (dense_rank last order by date_) as execution_id from abc group by id, name order by id; ID NAME DATE_ EXECUTION_ID ---------- ---- ---------- ------------ 1 AA 2019-09-09 11 2 BB 2019-09-09 44

If ID and name are not always the same, and you want the name form the latest date too, then use the same pattern:

select id, max(name) keep (dense_rank last order by date_) as name, max(date_) as date_, max(execution_id) keep (dense_rank last order by date_) as execution_id from abc group by id order by id;

which gets the same result with your sample data.

With lots of columns it's probably simpler to use a subquery (CTE or inline view) with a ranking function and a filter (as @Lukasz shows).

forpas · Accepted Answer · 2019-09-09 16:52:57Z

With NOT EXISTS:

select t.* from ABC t where not exists ( select 1 from ABC where "Id" = t."Id" and "Name" = t."Name" and "Date" > t."Date" )

I used and name = t.name only because you have it in your code.
If it is not needed you can remove it.
See the demo.
Results:

Id | Name | Date | Execution id -: | :--- | :---------| -----------: 1 | AA | 09-SEP-19 | 11 2 | BB | 09-SEP-19 | 44

Collectives™ on Stack Overflow

Retrieve max date for distinct IDs in a table [duplicate]

3 Answers 3

1 Comment

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Linked

Related