I am new to SQL, I am not able to solve this scenario.

I have this table:

Number      code    d_date 
---------------------------------------
2054330994  SP41   11/23/2020 10:14:43
2054330994  SP41   11/23/2020 10:14:44
2054330994  SP41   11/23/2020 10:14:45
2054330994  SP41   11/23/2020 10:14:46
2054330994  SP41   11/23/2020 10:14:47
2054330994  SP41   11/23/2020 10:14:48
2054330994  SP41   11/23/2020 10:14:49
2054330994  SP41   11/23/2020 10:14:49
2054330994  SP41   11/23/2020 10:14:49
2054330994  SP41   11/23/2020 10:14:43
2054330995  SP41   11/23/2020 10:14:43
2054330995  SP41   11/23/2020 10:14:52
2054330995  SP41   11/23/2020 10:14:43
2054330995  SP41   11/23/2020 10:14:52
2054330995  SP41   11/23/2020 10:14:52

Expected results:

2054330994  SP41   11/23/2020 10:14:49
2054330995  SP41   11/23/2020 10:14:52

We can see there is many duplicate entry.

My issue: I need to keep the number, which d_date has latest entry. Remaining entry related this number we need to removed from same table.

Sorry I am not able to get going on this.

Thanks in advance.

CodePudding user response：

Delete from the table, as you said:

Before:

SQL> select * from test order by num, code, d_date;

       NUM CODE D_DATE
---------- ---- --------------------
       994 SP41 11/23/2020 10:14:43
       994 SP41 11/23/2020 10:14:43
       994 SP41 11/23/2020 10:14:46
       994 SP41 11/23/2020 10:14:49
       994 SP41 11/23/2020 10:14:49
       995 SP41 11/23/2020 10:14:43
       995 SP41 11/23/2020 10:14:52
       995 SP41 11/23/2020 10:14:52
       995 SP41 11/23/2020 10:14:52

9 rows selected.

Delete:

SQL> delete from test a
  2    where a.rowid not in
  3              (select rowid
  4                 from (select row_number () over (partition by num, code
  5                                                  order by d_date desc) rn,
  6                              rowid
  7                       from test
  8                      )
  9                where rn = 1
 10              );

7 rows deleted.

After:

SQL> select * from test order by num, code, d_date;

       NUM CODE D_DATE
---------- ---- --------------------
       994 SP41 11/23/2020 10:14:49
       995 SP41 11/23/2020 10:14:52

SQL>

CodePudding user response：

To query without the dups you could aggregate on Number & code

SELECT "Number", code
, MAX(d_date) AS d_date
FROM your_table
GROUP BY "Number", code
ORDER BY "Number", code

Or use ROW_NUMBER

SELECT "Number", code, d_date
FROM
(
    SELECT "Number", code, d_date
    , ROW_NUMBER() OVER (PARTITION BY "Number", code ORDER BY d_date DESC) AS rn
    FROM your_table
) q
WHERE rn = 1
ORDER BY "Number", code;

To remove the dups from the table, then row_number can also be used for that.

DELETE 
FROM your_table
WHERE rowid IN (
   SELECT rowid
   FROM (
    SELECT "Number", code, d_date
    , ROW_NUMBER() OVER (PARTITION BY "Number", code 
                         ORDER BY d_date DESC) AS rn
    FROM your_table
   ) q WHERE rn > 1
);

SELECT *
FROM your_table
ORDER BY "Number", code, d_date

Demo on db<>fiddle here