MySQL script for deleting specific rows

Question

I have a MySQL table like this:

--------------------------------------------------
| Field | Type    | Null | Key | Default | Extra |
--------------------------------------------------
| A     | int(11) | NO   | PRI | NULL    |       |
--------------------------------------------------
| B     | int(11) | NO   | PRI | NULL    |       |
--------------------------------------------------
| C     | int(11) | NO   |     | NULL    |       |
--------------------------------------------------

I want to delete ALL rows for each A values, where C value is not in the top 10 (max) C value for that specific A. So there would remain 10 values for the first A, 10 values for the second A, 10 values for the third...

Thank you

Here is an example:

-------------
| A | B | C |
-------------
| 1 | 2 | 5 |
-------------
| 1 | 3 | 2 |
-------------
| 1 | 5 | 9 |
-------------
| 1 | 4 | 7 |
-------------
| 1 | 8 | 4 |
-------------
| 2 | 1 | 5 |
-------------
| 2 | 3 | 8 |
-------------
| 2 | 5 | 7 |
-------------
| 2 | 4 | 6 |
-------------
| 2 | 7 | 9 |
-------------
| 2 | 8 | 1 |
-------------

And let's say I only want the top 2, not the top 10. Then the result:

-------------
| A | B | C |
-------------
| 1 | 5 | 9 |
-------------
| 1 | 4 | 7 |
-------------
| 2 | 7 | 9 |
-------------
| 2 | 3 | 8 |
-------------

i cant think .give example how you want , and what u tried . — echo_Me
– echo_Me, Commented Jan 11, 2013 at 8:50
I wrote an example. Sorry if it's not clear. I can do it with a cursor, but it's slow for millions of rows. — recis
– recis, Commented Jan 11, 2013 at 8:51

echo_Me · Accepted Answer · 2013-01-11 17:59:51Z

1

you can use union between the max and the under max value like that

  SELECT A,B,t1.C from table1 t1
  INNER JOIN (select max(C) max1 from table1) t2
  ON t1.C= t2.max1 

  UNION

  SELECT A,B,MAX(C) MAX1 from table1 t3
  INNER JOIN (select  MAX(C) MAX2 from table1) t4
  WHERE t3.c < t4.max2 
  GROUP BY A

  ORDER BY A

and the output is like that

Obs : its just a try and a hint and a work around from me if you accept it ,u can fix it by your self. im not getting right numbers of B i dont know why. its in the second part of the UNION where getting the next max numbers

here DEMO ON SQLFIDDLE

edited Jan 11, 2013 at 17:59

answered Jan 11, 2013 at 13:12

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sashi Kant · Accepted Answer · 2013-01-11 09:07:02Z

0

You can write a procedure for this::

First get all the unique values of A,

Select DISTINCT A from table

Run a cursor on this result.

Inside the cursor,

Select * from table where A=$$cursorvalue order by C desc limit 2

answered Jan 11, 2013 at 9:07

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

1 Comment

recis Over a year ago

Yes, I can do that, but it will be very slow for millions (or even billions) of rows. That's why I want a simple "DELETE FROM ... WHERE ..." script.

Collectives™ on Stack Overflow

MySQL script for deleting specific rows

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related