ktos1234 ktos1234 - 11 days ago 5
SQL Question

Delete duplicated records Firebird SQL

I want to delete duplicated rows, so I used this select statement to find all duplicated rows.

SELECT * FROM MY_CARD T1
INNER JOIN( SELECT IDCARD, YEAR FROM MYCARD GROUP BY IDCARD, YEAR HAVING COUNT(IDCARD) > 1 ) T2 ON T1.IDCARD = T2.IDCARD AND T1.YEAR=T2.YEAR
WHERE T1.IDRODZ = 5 AND IDCARD=80;


My result looks like that, but this is only short example, there are more duplicated records.

ID IDCARD YEAR IDRODZ
1 80 2014 5
2 80 2014 5
3 80 2014 5
4 80 2015 5
5 80 2015 5
6 80 2015 5


I need delete statement, that helps me to delete duplicated values, so that my table could look like that:

id IDCARD YEAR IDRODZ
1 80 2014 5
4 80 2015 5


How can I achieve it?

Answer

As you have a unique ID, you can do something like this:

delete from my_card
where id not in (select min(id)
                 from my_card
                 group by idcard, year);

That will keep the rows with the smallest id for each (idcard, year) combination. If you want to keep the biggest id, use max(id) in the sub-select.

Online example: http://rextester.com/WXTU26543