0

I'm new to sql and I can't work out how to delete duplicate rows, I have a table like this called 'till_total':

till_id total
1 80
1 80
1 60
2 30
2 30
2 50

I want to only delete full duplicate rows so the table ends up like this

till_id total
1 80
1 60
2 30
2 50

I wrote this code to try and do it

SELECT till_id, total, COUNT(*) AS CNT
FROM till_total
GROUP BY till_id, total
HAVING COUNT(*) > 1
ORDER BY till_id;

But that seems to delete all rows where the till_id is repeated. Could anyone help me with this?

MT0
  • 143,790
  • 11
  • 59
  • 117
El Louise
  • 3
  • 2

2 Answers2

1

Good, old ROWID approach:

Before:

SQL> select * from till_total;

   TILL_ID      TOTAL
---------- ----------
         1         80
         1         80
         1         60
         2         30
         2         30
         2         50

6 rows selected.

Delete duplicates:

SQL> delete from till_total a
  2  where a.rowid > (select min(b.rowid)
  3                   from till_total b
  4                   where b.till_id = a.till_id
  5                     and b.total = a.total
  6                  );

2 rows deleted.

After:

SQL> select * from till_total;

   TILL_ID      TOTAL
---------- ----------
         1         80
         1         60
         2         30
         2         50

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0
WITH till_total AS (
  SELECT till_id
     row_number() OVER(PARTITION BY till_id ORDER BY desc) AS row
  FROM TABLE
)
DELETE till_total WHERE row > 1

This might work for you, deleting rows that are more than 1 duplicate, not less than 1.

MiNi
  • 11
  • 3