>>>Hi everybody,
>>>
>>>I have a complex query to get duplicates. I want to change all records (using SCAN/ENDSCAN) in the main table except for the latest record in each group. I can write a procedural code, but I'm wondering if there is a way to get them by SQL?
>>>
>>>I guess the procedural code would be simpler anyway.
>>>
>>>Thanks in advance.
>>Do you have unique id in each record? Can you show an example of the structure of your main table?
>
>Yes, I have the PK in each record.
>
>PK, FK, Date1, Date2, Flag
>
>I need to update all duplicates by FK and Flag = 1 to set flag = 0 except for the latest by Date1 and Date2. I already did it by procedural code, but curious if it's possible to get the records to update by SQL.
Another solution you can use something like

SELECT pk,fk,flag,date1,date2 FROM test WHERE Flag=1 AND DATE1 = (SELECT MAX(Date1) FROM Test T2 WHERE t2.fk=test.fk AND t2.flag=test.flag) AND DATE2 = (SELECT MAX(Date2) FROM Test T3 WHERE t3.fk=test.fk AND t3.flag=test.flag)