Re: Get "Youngest" Record in Child Table ?

Your post specified doing this with a join, but do you have the option to create a function, like

    Get_Last_Event(Passed_Person Number) Returns Varchar2 Is

            v_retval t2_PersonEvents.evt_type%Type;
            Cursor Get_Last_Date Is
                Select evt_type
                    From t2_PersonEvents
                    Where perid = Passed_Person
                    Order By evt_date Desc;
        Begin
            Open Get_Last_Date;
            Fetch Get_Last_Date Into v_retval;
            Close Get_Last_Date;
            Return(v_retval);
        End Get_Last_Event;

Then you could just select it like a column.

    Select Name, Get_Last_Event(ID) as Latest_Event From t1_Person;

Best practice? I'll let other posters decide (as I'm sure they will)! Rail away...

Joe

"Richard Shea" <richardshea_at_despammed.com> wrote in message 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.
Received on Wed Nov 24 2004 - 15:53:39 CST