Mysql - Delete All except 2 rows of all post_id1 from a table -


i have 1 table abc has 1 milion+ rows :

post_id1    post_id2    count     22      100218      1     22      100225      2     22      100432      1     22      100719      5     22      100807      4     22      100827      3     22      100934      22     22      101322      1     27      101613      10     27      101931      1     29      103783      1     29      104328      16     29      104345      1     29      104356      7 

in table want keep 2 rows of each post_id1 value (any 2 rows)

like output be

post_id1    post_id2    count     22      100218      1     22      100225      2     27      101613      10     27      101931      1     29      103783      1     29      104328      16 

what sql query should run?

post_id1 can number.

thanks

assuming postid_2 unique shown in example. can use below query delete.

delete test post_id2 not in ( select post_id2 (       select post_id2 test       (select count(*) test t               t.post_id1=test.post_id1 ,               t.post_id2>=test.post_id2)<=2) t); 

here sorted based on post_id2(top 2 maximum displayed). can check demo here in sqlfiddle


Comments

Popular posts from this blog

java - UnknownEntityTypeException: Unable to locate persister (Hibernate 5.0) -

python - ValueError: empty vocabulary; perhaps the documents only contain stop words -

ubuntu - collect2: fatal error: ld terminated with signal 9 [Killed] -