Hi Sergey,

I have a table with lots of fields. I need to select records with maximum date grouped by address. We may have records with the same address and same date (maximum), but different info in other fields (for instance, one record would represent a sale and another record would represent mortgage) and they both were quite recent.

Here is the test program, I just wrote:

tcTableName = getfile('dbf')
lcFile = justfname(m.tcTableName)+"TempTest"
select upper(CCODE+TOWN+STREET+str(STNUM,4)+STNUMEXT+UNIT) ;
       as Address, max(date) as MaxDate from (m.tcTableName) ;
       group by 1 into cursor curTemp
       
select upper(CCODE+TOWN+STREET+str(STNUM,4)+STNUMEXT+UNIT) ;
       as Address, * from (m.tcTableName) tbl ;
       inner join curTemp on ;
       upper(tbl.CCODE+tbl.TOWN+tbl.STREET+str(tbl.STNUM,4)+tbl.STNUMEXT+tbl.UNIT) = ;
       curTemp.Address and tbl.Date = curTemp.MaxDate 

*!*	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 I'm not sure, I get it right... I could not figure out to do it in one SQL, though it's not a goal (if it could not be done in 1 select, I can use multiple)


>If address is the same than what difference date makes?
>
>>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.