Re: sql question

Hmmm... I believe the following might work:

select *
from table1 T1, table2 T2
where T1.unique_identifier = T2.unique_identifier and T2.date_of_insertion in (

	select max(date_of_insertion)
	from table2 T2b
	where t2b.unique_identifier = t2.unique_identifier
	and t2b.start_date = t2.start_date
	);

Jonathan

On Sun, 4 Oct 1998 10:30:07 +0200, "Patrick S" <burcht61_at_hotmail.com> wrote:

>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 - 17:34:42 CDT