Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Get "Youngest" Record in Child Table ?
Hi - I've got a table which records 'events' in the life of it's
parent. Like this ...
create table t1_Person (id number(1),name varchar2(10)); create table t2_PersonEvents (perid number(1),evt_type varchar2(12),evt_date date);
-- insert into t1_Person values (1,'Fred'); insert into t1_Person values (2,'Mary'); -- insert into t2_PersonEvents values (1,Received on Wed Nov 24 2004 - 15:01:41 CST
'Birth',to_date('1950-1-1','YYYY-MM-DD'));
insert into t2_PersonEvents values (1,
'Graduation',to_date('1971-1-1','YYYY-MM-DD'));
insert into t2_PersonEvents values (1,
'Marriage',to_date('1973-1-1','YYYY-MM-DD'));
insert into t2_PersonEvents values (2,
'Birth',to_date('1970-1-1','YYYY-MM-DD'));
insert into t2_PersonEvents values (2,
'Graduation',to_date('1991-1-1','YYYY-MM-DD'));
commit; -- What I'm interested to know is a good efficient way to join the tables so that I can view the most recent event for each person (based upon evt_date) alongside the columns of t1_Person. I've got a number of ways I'm doing it but in real life the parent tables are considerably larger than shown here and I would appreciate some 'best practice' advice. In case it matters in my real world situation the ratio of Children to Parents is something like 10-20:1 (ie the parents don't have terribly eventful lives ;-). Just to summarise I would like output that looked like this ... Name Latest Event ==== ============ Fred Marriage Mary Graduation Thanks Richard.
![]() |
![]() |