Re: sql return only first matching record from join table

>jbarney_ca_at_yahoo.ca (Barnoit) wrote in message news:<cd2c60c8.0207040634.1bbc5deb_at_posting.google.com>...
>> I posted this in a follow-up to Martin, but I think the answer to my
>> second question is to place the (select min(rowid)...)as x in the from
>> clause and alias that (x) and then use an outer join on x in the where
>> clause something like ... and occupant.rowid(+) in x.rowid. This seems
>> to work, but if anyone has any suggestions/improvements I'd appreciate
>> hearing them.
>>
>> Thanks
>>
>

Try using an inline view as in:

select a.address, o.min_occ
from
address_table a,
(
 select address_id, min(occupant) min_occ  from occupant_table group by address_id ) o
where a.address_id = o.address_id (+)
;

Gene Hubert
Durham, NC Received on Fri Jul 05 2002 - 12:36:00 CDT