Hi everybody,

I have a simple problem. I need to group records by address and select the records with maximum date (if we would have two or more same dates for the same address, I need all these records).

My original SQL:

select upper(...) as Address, max(date) as MaxDate, * from .. group by 1

obviously produced incorrect results (but it took us a while to discover this problem).

Here is the SQL I just tried:

select upper(CCODE+TOWN+STREET+str(STNUM,4)+STNUMEXT+UNIT) as Address, * ;
       from (m.tcTableName) fr where date = (select max(date) ;
       from (m.tcTableName) se where ;
       upper(fr.CCODE+fr.TOWN+fr.STREET+str(fr.STNUM,4)+fr.STNUMEXT+fr.UNIT) = ;
       upper(se.CCODE+se.TOWN+se.STREET+str(se.STNUM,4)+se.STNUMEXT+se.UNIT)) ;
       order by 1 into table (m.lcFile)

but it gives me an error: SQL: Error correlating fields (Error 1801)

Could you please tell me, how this SQL should be re-written?

Thanks a lot in advance.