CODEWITHSUNDEEP
sqlsql-server
Fauzi Rachman
Fauzi Rachman

Reputation: 101

Delete Specific Duplicate Data with Condition

I have a table which contains some duplicated data and I want to delete specific entries from it based on the value stored in the Time column.

In my table, ID: 504289 has 3 entries:

Date       Time              Place  ID
20161128   2016-11-30 12:54  East   504289
20161128   2016-11-30 12:55  East   504289
20161128   2016-11-30 13:10  East   504289

I want to delete only the entries where the time interval between it and previous entries have a difference of 15 minutes or less.

Applied to the table above, only the 12:55 record will be removed because 1254 is the first record and 13:10 is greater than 15 minutes from it.

Can anyone help figure out the query?

Upvotes: 1

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Using standard SQL, you can delete a records that are within 15 minutes of another record. Assuming your time column is some sort of date/time type for the database you are using, the SQL would look something like this:

delete from t
    where exists (select 1
                  from t t2
                  where t2.date = t.date and
                        t2.time < t.time and
                        t2.time > t.time - interval '15 minute'
                 );

Date/time arithmetic varies significantly among databases. The above is a general form that gives the shape of the query.

Upvotes: 2

Related Questions