>>>>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)
>
I think it would not work. It doesn't solve the problem of the thread either.
If it's not broken, fix it until it is.
My Blog