Nadya,

PMFJI. This is one place that VFP is very confusing. Any time you use GROUP BY in a SQL statement, you cannot be sure of any data that is not in the GROUP BY clause or in an aggregate function (MAX, MIN, AVG, etc). In T-SQL on SQL server, including data other than the above will give you an error. In VFP, it just runs and returns whatever it wants. E.g.

Select Invoice_PK, MAX(date), customer_FK from Invoice ;
group by customer_FK

will give you the max invoice date per customer, but you cannot count on the fact that the invoice_PK returned is the PK of the invoice with the max date. The above would give an error in SQL server because the invoice_PK is not in the group by clause or an aggregate function.

It's a lesson I too learned the hard way.

I believe that what Sergey gave you in msg 705998 is the correct approach.

John

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