Geek Answers Handbook

Duplicate removal request using GROUP BY

id_specific_price    id_product  
-------------------------------
            1                2  
            2                2  
            3                2  
            4                3  
            5                3  
            6                3  
            7                3

It is necessary to remove duplicates, the expected result:

id_specific_price    id_product  
-------------------------------
            3                2  
            7                3

SELECT * 
  FROM ps_specific_price 
 WHERE id_specific_price NOT IN 
 (SELECT MAX(id_specific_price) 
    FROM ps_specific_price 
   GROUP BY id_product)

works but

DELETE FROM ps_specific_price 
 WHERE id_specific_price NOT IN 
(SELECT MAX(id_specific_price) 
   FROM ps_specific_price 
  GROUP BY id_product)

no. There are many examples to get around this, but for some reason I cannot adapt it. I believe this is GROUP BY. For example:

DELETE FROM ps_specific_price 
 WHERE id_specific_price NOT IN
 (SELECT MAX(p.id_specific_price) 
    FROM (SELECT * FROM ps_specific_price ) as p)
   GROUP BY id_product

Where am I wrong here?

+4
source share
3 answers

If you are looking for a solution for MySQL, you can use the correct multi tableDELETE syntax along with JOINlike this

DELETE p
  FROM ps_specific_price p JOIN
(
  SELECT id_product, MAX(id_specific_price) id_specific_price
    FROM ps_specific_price
   GROUP BY id_product
) d 
   ON p.id_product = d.id_product
  AND p.id_specific_price <> d.id_specific_price;

Result:

| ID_SPECIFIC_PRICE | ID_PRODUCT |
| ------------------- | ------------ |
| 3 | 2 |
| 7 | 3 |

Here is the SQLFiddle demo

+9
source

:

CREATE TABLE ps_specific_price (
  id_specific_price NUMBER,
  id_product NUMBER
);

INSERT INTO ps_specific_price (id_specific_price, id_product) VALUES (1, 2);
INSERT INTO ps_specific_price (id_specific_price, id_product) VALUES (2, 2);
INSERT INTO ps_specific_price (id_specific_price, id_product) VALUES (3, 2);
INSERT INTO ps_specific_price (id_specific_price, id_product) VALUES (4, 3);
INSERT INTO ps_specific_price (id_specific_price, id_product) VALUES (5, 3);
INSERT INTO ps_specific_price (id_specific_price, id_product) VALUES (6, 3);
INSERT INTO ps_specific_price (id_specific_price, id_product) VALUES (7, 3);

COMMIT;

DELETE FROM ps_specific_price ps
  WHERE ps.id_specific_price NOT IN (
    SELECT MAX(id_specific_price)
      FROM ps_specific_price ps_in
    WHERE ps_in.id_product = ps.id_product
    );

SELECT * FROM ps_specific_price;

ID_SPECIFIC_PRICE      ID_PRODUCT             
---------------------- ---------------------- 
3                      2                      
7                      3

.

Oracle 11g R2. SQLFiddle, DELETE MySQL - , , .

+1

, :

ps_specific_price WHERE (id_product, id_specific_price) NOT IN (SELECT id_product, MAX (id_specific_price) FROM ps_specific_price GROUP BY id_product);

Teradata, .

, , , id_product . delete , pid .

, .

0

More articles:


All Articles