Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Get "Youngest" Record in Child Table ?

Get "Youngest" Record in Child Table ?

From: Richard Shea <richardshea_at_despammed.com>
Date: 24 Nov 2004 13:01:41 -0800
Message-ID: <d2a3bd.0411241301.1fb75419@posting.google.com>


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,

'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.
Received on Wed Nov 24 2004 - 15:01:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US