Retrieve max date for distinct IDs in a table

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?

score 3 · Accepted Answer · answered Sep 09 '19 at 16:37

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 09 '19 at 16:37

Lukasz Szozda

162,964
23
234
275

1

Thank you. Looks like it works for me. – Vic VKh Sep 09 '19 at 17:24

score 3 · Answer 2 · answered Sep 09 '19 at 16:41

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

Thank you. Looks good too as the Lukasz solution – Vic VKh Sep 09 '19 at 17:33 — Vic VKh, Sep 09 '19 at 17:33

forpas · Answer 3 · 2019-09-09T16:52:57.763

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

Retrieve max date for distinct IDs in a table

3 Answers3

Related