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

Basically you will be updating your table using the PK from ..

selecting TOP 1 PK from (your duplicate selection statement) order by Date1 desc

and

selecting TOP 1 PK from (your duplicate selection statement) order by Date2 desc