Re: please help me in building a SQL query...........

> select select p.personuid, a. addresstext
> from person2address p, address a
> where a.addressguid = p.addressguid
> and (a.addressguid, a.validitystartdate) in
> (select addressguid, max(validitystartdate)
> from address
> group by addressguid);
>

this query doesn't work for the case

personuid addressuid

3             7
3             8

addressuid addresstext validitystartdate

7              mnop         04-08-80
8              huil         04-08-80

because it returns both of the addresses for the personuid "3". That's why i proposed max(addresstext) to get one only, but i didn't expect there were some other fields in that table address. Look my other answer i think it works good but there should be something easier again. Received on Wed Jan 05 2005 - 08:06:57 CST