Hi John,

I knew this fact actually. I just don't know, what I was thinking, writting the program... When the user finally found a problem (after ~6-7 months in production), I spent whole day looking to this project and could not find, what's wrong. Two other persons looked into this program, made suggestions, but not related to the source of the problem. Finally on Monday I looked into the file created with my manager and then we found the problem...

So, I wrote it in two SQL. Cetin's suggestion worked extremely slow, so I leave it as 2 SQLs bor better speed.

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