Re: Get "Youngest" Record in Child Table ?

"Richard Shea" <richardshea_at_despammed.com> a écrit dans le message de news:d2a3bd.0411241301.1fb75419_at_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.

select t1.name, t2.evt_type "Latest Event" from (select id, evt_type, row_number() over (partition by id order by evt_date desc) rn from t2_PersonEvents) t2,

        t1_Person t1
where t1.id = t2.id and t2.rn = 1
/

-- 
Regards
Michel Cadot