>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.

Nadya,
Separate the fields and alias the 'date'. ie:

upper(fr.CCODE) == upper(se.CCODE) and ...

Cetin