Delete query issue

I want to delete the records having same name and price so that oldest record should stay there. Below is a sample:

ID Name Date Price

1 KDL1 19-06-2015 25
2 KDL1 01-07-2015 25
3 KDL1 15-05-2015 20

4 KDL2 05-05-2015 22
5 KDL2 08-05-2015 22
6 KDL2 09-06-2015 32

7 KDL3 02-06-2015 25
8 KDL3 04-06-2015 25
9 KDL4 10-06-2015 95

Result should be:
1 KDL1 19-06-2015 25
3 KDL1 15-05-2015 20
4 KDL2 05-05-2015 22
6 KDL2 09-06-2015 32
7 KDL3 02-06-2015 25
9 KDL4 10-06-2015 95

What did you try?

exact syntax depends on which dbms you’re using, which you neglected to mention

1 Like

I am trying in MySQL.

DELETE daTable FROM ( SELECT name , price , MIN(dat) AS oldest FROM daTable GROUP BY name , price) AS ttt INNER JOIN daTable ON daTable.name = ttt.name AND daTable.price = ttt.price AND daTable.dat > ttt.oldest

Great many thanks :slight_smile:

There is one issue that if there are multiple records having same name, price and date then their duplicated should also get removed. For example:

ID Name Date Price

1 KDL1 19-06-2015 25
2 KDL1 19-06-2015 25
3 KDL1 19-06-2015 20

It should return:
1 KDL1 19-06-2015 25
3 KDL1 19-06-2015 20

you can do this with a second query

your first query removed “duplicates” based on same name and price, keeping oldest date

it involved a subquery which did a GROUP BY on two columns

now you want to remove “duplicates” based on same name and price and date, keeping lowest/highest id value

so use a subquery which does a GROUP BY on three columns

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.