How remove from group in table

Question

Exists table temporary_meta_product with structure

CREATE TABLE `temporary_meta_product` (
  `meta_product_id` int(11) NOT NULL,
  `product_code` int(11) NOT NULL,
  UNIQUE KEY `META_PRODUCT_UNIQUE` (`meta_product_id`,`product_code`)
) ENGINE=InnoDB;

and data

+ -------------------- + ----------------- +
| meta_product_id      | product_code      |
+ -------------------- + ----------------- +
| 54306361             | 3533719           |
| 54306361             | 3533720           |
| 54306361             | 3533721           |
| 54306361             | 3533722           |
| 54306361             | 3533723           |
| 54306361             | 3533724           |
| 54306361             | 3533725           |
| 54306361             | 3533726           |
| 54306361             | 3533727           |
| 54306361             | 3533728           |
+ -------------------- + ----------------- +
| 54306577             | 3533870           |
| 54306577             | 3533871           |
| 54306577             | 3533872           |
| 54306577             | 3533873           |
| 54306577             | 3533874           |
| 54306577             | 3533875           |
| 54306577             | 3533876           |
| 54306577             | 3533877           |
| 54306577             | 3533878           |
| 54306577             | 3533879           |
+ -------------------- + ----------------- +

Need reduce table or create new reduced I new table need 5 rows or less for all meta_product_id, expect:

+ -------------------- + ----------------- +
| meta_product_id      | product_code      |
+ -------------------- + ----------------- +
| 54306361             | 3533719           |
| 54306361             | 3533720           |
| 54306361             | 3533721           |
| 54306361             | 3533722           |
| 54306361             | 3533723           |
| 54306577             | 3533870           |
| 54306577             | 3533871           |
| 54306577             | 3533872           |
| 54306577             | 3533873           |
| 54306577             | 3533874           |
| 54306784             | 3534021           |
| 54306784             | 3534022           |
+ -------------------- + ----------------- +

Check this answer https://stackoverflow.com/a/13187418/3470178 — Juan Carlos Oropeza, Aug 21 '17 at 14:32

etsa · Accepted Answer · 2017-08-21T15:02:18.843

0

Naming (for brevity in my example) your table as t1, you can try following query:

SELECT meta_product_id, product_code 
 FROM (
     SELECT meta_product_id, product_code
     ,@r:= CASE WHEN @g=t1.meta_product_id THEN  @r+1 ELSE 1 END consecutive
     ,@g:=t1.meta_product_id
     FROM t1
     CROSS JOIN (SELECT @r:=1, @g:=-1) t1
     ORDER BY meta_product_id, product_code
     ) A
 WHERE consecutive <=5;

edited Aug 21 '17 at 15:02

answered Aug 21 '17 at 14:35

etsa

5,020
1
7
18

You can use `IF(@g=t1.meta_product_id, @r+1, 1) ` – Juan Carlos Oropeza Aug 21 '17 at 14:41
But you should use `IF(@g=t1.meta_product_id, @r+1, IF(@g:=t1.meta_product_id, 1, 1))` because assignment order for user variables isnt guaranted https://dev.mysql.com/doc/refman/5.7/en/user-variables.html `However, the order of evaluation for expressions involving user variables is undefined.` – Juan Carlos Oropeza Aug 21 '17 at 14:43
@Juan Thanks for your observation. But do you think it's also the case when there are two different variables? In the same manual you cited, some paragraphs later, they write "To avoid problems with this behavior, either do not assign a value to and read the value of the same variable within a single statement..." so it could seem they refer only at the "same variable". – etsa Aug 21 '17 at 14:54
I learn that from Gordon (if you know who he is). So I rather be safe than sorry. – Juan Carlos Oropeza Aug 21 '17 at 15:00
@Juan I already saw I forgot it and added, thanks – etsa Aug 21 '17 at 15:03

How remove from group in table

1 Answers1