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

techimadhu wrote:
> thanks for replying.
> actually i have some more fields in address table which i want to
> display, but i cant apply max to all of them.

The previous reply doesn't help that much, in that max(Addresstext) is not what you need returned. Your requirement dictates the use of a subquery to return the max(validitystartdate) and the addressguid associated with it:

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);

Something along this line would allow you to return any number of values in the driving select and still use the max(validitystartdate) to isolate the records you need.

David Fitzjarrell Received on Wed Jan 05 2005 - 07:52:36 CST