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
r937
July 1, 2015, 1:35pm
3
exact syntax depends on which dbms you’re using, which you neglected to mention
1 Like
r937
July 2, 2015, 8:56am
5
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
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
r937
July 4, 2015, 4:19pm
8
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
system
Closed
October 3, 2015, 11:22pm
9
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.