One more try... using your SQL statement

I re-read Sergey's... mine is slightly different where I use the MAX() of the combination of address and date. By useing just the date, you return similar results as SELECT *, Max(DATE) which has proven to be incorrect.

By selecting the max of both the address and the date, you basically fuse the two together before the MAX() is performed. If you have the same address records with two different dates, only the MAX(DATE) will come across. Also as you requested, if you have the same address and same date, both will come across. Of course SELECT DISTINCT would filter those out too if you want.


I did forget one thing though, the inner Group By clause.

select upper(CCODE+TOWN+STREET+str(STNUM,4)+STNUMEXT+UNIT) as Address, * ;
from address where upper(CCODE+TOWN+STREET+str(STNUM,4)+STNUMEXT+UNIT) + TRANSFORM(date) IN ;
(select MAX(upper(CCODE+TOWN+STREET+str(STNUM,4)+STNUMEXT+UNIT) + TRANSFORM(date)) ;
from address
GROUP BY CCODE, TOWN, STREET, STNUM, STNUMEXT, UNIT) ;
order by 1 into table (m.lcFile)