Re: sql question

create view temp_view(unique_identifier varchar2(10), start_date date, amount number, date_of_insertion date) as select a.unique_identifier, a.start_date, a.amount, a.date_of_insertion
from table2 a
where a.date_of_insertion>=all(select b.date_of_insertion from table2 b where a.unique_identifier=b.unique_identifier and a.start_date = b.start_date
and a.amount = b.amount);

create view final_view(field_1 vachar2(30),unique_identifier varchar2(10), start_date date, amount number, date_of_insertion date) as select b.field_1,a.unique_identifier, a.start_date, a.amount, a.date_of_insertion
from table1 b, temp_view a
where a.unique_identifier=b.unique_identifier;

Wei

Patrick S <burcht61_at_hotmail.com> wrote in article <6v7bp3$8pq$1_at_trex.antw.online.be>...
> Hi,
>
> i've got two tables :
>
> table 1 : unique_identifier varchar2(10)
> field_1 varchar2(30)
>
> table 2 : unique_identifier (same as table 1) varchar2(10)
> start_date date
> amount number
> date_of_insertion date
>
> table 2 contains detail information of table 1. Users can only insert
> lines, no updates or deletes are allowed.
>
> In table 1 is only one record per unique identifier
>
> Table 2 contains an undefined number of rows for a unique identifier. It
is
> possible to have more rows for a start_date, and the date of insertion
> (sysdate) is the date that determines the most recent insert
>
> How do i write a view that gives me the most recent insert for a
start_date
> (not necessarily the highest start_date) for a unique_identifier
>
> for example
>
> 2 lines on 01-jan-90
> 3 lines on 01-jan-91
> 5 lines on 01-jan-92
> 3 lines on 01-jan-93
> 2 lines on 01-jan-94
> ...
>
> Can i with a VIEW find the last inserted line (most recent date of
> insertion) for start_date 01-jan-92 for a unique identifier ?
> I also need all the columns of both tables in the view !
>
> Thanks
> Patrick SOETENS
> burcht61_at_hotmail.com
>
>
>
>
>
Received on Mon Oct 05 1998 - 23:13:25 CDT