CODEWITHSUNDEEP
sqloracle-databasequery-optimization
dijxtra
dijxtra

Reputation: 2751

Finding maxiumum in a group with a single select

I have a group of children for each parent, and a date for each child, all in one table. I'd like to: for each parent, find children with max date. I wrote a statement which does just that:

create table t(
  parent string,
  child string,
  date date,
);

primary key: (parent, child)

select a.*
from
    (select parent, child, date from t) a
  join
    (select parent, max(date) as lastdate from t group by parent) b
  on
    a.parent = b.parent
    and a.date = b.lastdate

Now problem is, I do 2 separate selects on table t. Let's say table t is huge and I'd like to do this with just one select on table t. Is that possible?

I'm using Oracle SQl Developer on Oracle 10g EE.

Upvotes: 2

Views: 136

Answers (2)

Nick
Nick

Reputation: 2514

As you are needing to capture all children with the same max date, if I read your original question correctly. The rank operator will rank all children with the same date as the same rank. I changed your table slightly so it would work and not conflict with Oracle keywords. 

    create table t(
  parent varchar2(20),
  child varchar2(20),
  child_date date
);

insert into t (parent, child, child_date) values (1,'A',sysdate-1);
insert into t (parent, child, child_date) values (1,'B',sysdate+45);

insert into t (parent, child, child_date) values (2,'A',sysdate+45);
insert into t (parent, child, child_date) values (2,'B',sysdate+45);

select parent, child from (
    select parent, child, rank() over (partition by parent order by child_date desc) rnk
    from t)
    where rnk = 1;

Upvotes: 0

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

You can use the aggregate function FIRST:

SELECT parent, 
       MAX(child) KEEP (DENSE_RANK FIRST ORDER BY date DESC) lastchild, 
       MAX(date) lastdate 
  FROM t
 GROUP BY parent

The lastchild column will return the value of child for the row that has the maximum date for this parent. In case of tie (several children have the same max date), MAX will be applied.

Upvotes: 5

Related Questions