CODEWITHSUNDEEP
sql
Rakesh Kr Pandey
Rakesh Kr Pandey

Reputation: 1

Remove duplicates, keep most recent record

Original post title: query to delete duplicate values from a field on the besis of maximum value in that column of a table by keeping recent one

I have a table named Idle_info: columns are

open_date(date/time),time_from(date/time),duration(number),user(text)

I want to delete the values if open_date and time_from is same. Only that value will be save where duration is maximum.

I tried this:

DELETE FROM Idle_info 
WHERE idle_duration NOT IN (SELECT MAX(i.idle_duration) 
FROM Idle_info i 
WHERE i.open_date = open_date AND i.time_from=time_from AND i.user=user 
GROUP BY i.open_date,i.time_from,i.idle_duration,i.user

but not working

thanks

Upvotes: 0

Views: 4554

Answers (2)

ksa
ksa

Reputation: 403

Try this:

DELETE FROM Idle_info r1
WHERE exists (select 1 
                  from idle_info r2 
                  where r1.user=r2.user 
                    and r1.time_from=r2.time_from 
                    and r1.open_date=r2.open_date
                    and r1.idle_duration<r2.idle_duration
              )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This is your query:

DELETE FROM Idle_info
     WHERE idle_duration NOT IN (SELECT MAX(i.idle_duration)
                                 FROM Idle_info i
                                 WHERE i.open_date = open_date AND i.time_from = time_from AND
                                       i.user = user
                                 GROUP BY i.open_date,i.time_from, i.idle_duration, i.user
                                )

My guess is that you want to keep the the most recent record for a given set of criteria. You have two problems. One is the lack of correlation. The second is the group by clause. I believe the following does what you want:

DELETE FROM Idle_info i
     WHERE i.idle_duration < (SELECT MAX(i.idle_duration)
                              FROM Idle_info i2
                              WHERE i.open_date = i2.open_date AND
                                    i.time_from = i2.time_from AND
                                    i.user = i2.user
                             );

Upvotes: 1

Related Questions