delete records from the table sorted by two columns

Question

I have a table with these columns:

tel_number,date,time

There might be several records for a single tel_number. For each tel_number, I want to delete all except the record which has the most recent date from the table and in cases where there are multiple records for the most recent date, the one with the most recent time get to be selected and all the rest should be removed from the table.

for example from records like these:

1 2223333,14/01/28,08:30
2 2223333,14/01/27,08:30
3 2223333,14/01/28,16:30
4 2225555,14/01/27,10:34
5 2225555,13/12/29,10:34

all record except these two should be deleted:

3 2223333,14/01/28,16:30
4 2225555,14/01/27,10:34

edit:

I have tried this so far, but it doesn't delete records which has the same date but different times:

delete from table where (tel_number,date) not in 
(select tel_number,max(date) from table group by tel_number);

Select out the rows with the greatest dates, empty the table, and reinsert the data. This is a quite naive approach, but simple to implement. — Esoteric Screen Name, Jan 29 '14 at 05:22
@Narges Your initial approach is on the right track. Think about why your query doesn't find the records with the greatest time value. You are very close and just need to properly extend your attempt. — Esoteric Screen Name, Jan 29 '14 at 05:28
I think you can use something like `HAVING DateColumn = MAX(DateColumn)`, so you can get only the highest values or use `<>` so you can get all the other ones. Also the answers in that question are really amazing and could be useful http://stackoverflow.com/questions/3491329/group-by-with-maxdate — CodingMate, Jan 29 '14 at 05:31

score 1 · Answer 1 · edited Aug 16 '14 at 02:04

According to Deleting Duplicate Rows In Oracle:

1. Using MIN(rowid) : The most common method of removing duplicate rows.

DELETE FROM Table1
      WHERE ROWID NOT IN (SELECT   MIN (ROWID)
                              FROM Table1
                          GROUP BY tel_number, Date, Time);

2. Using MIN(rowid) & Join: More or less the same as first one

DELETE FROM Table1 t
      WHERE t.ROWID NOT IN (SELECT MIN (b.ROWID)
                              FROM Table1 b
                             WHERE b.tel_number = t.tel_number
                               AND b.Date = t.Date 
                               AND b.Time = t.Time);

3. Using Analytic functions

DELETE FROM Table1 
      WHERE ROWID IN (
               SELECT rid
                 FROM (SELECT ROWID rid,
                              ROW_NUMBER () OVER (PARTITION BY tel_number,Date, Time ORDER BY ROWID) rn
                         FROM Table1 )
                WHERE rn <> 1);

score 0 · Answer 2 · answered Jan 29 '14 at 08:19

I solved the problem with this query:

 delete from table a where (a.tel_number,a.DATE,a.TIME) not in 
    (
        select tel_number,date,max(time) 
        from table group by tel_number,date 
        having date in 
        (
            select max(date) from table group by tel_number
        )
    );

delete records from the table sorted by two columns

2 Answers2