Deleting rows

Databases
1

Hello.

Got something like this:


Date   | Value
================
today  | 9000
--------------
today  | 8000
--------------
today  | 7000
--------------
today  | 6000
--------------
today  | 5000
--------------
today  | 4000
--------------
today  | 3000
--------------
today  | 2000
--------------

I want to remove all the rows omitting 5 top rows.

We can select rows ommiting 5 top by:


SELECT * FROM stats
ORDER BY value
LIMIT 5,10

but cannot achieve , delete action.

Any proposals will be appreciated.

2

does your table have a primary key?

also, how often do you want to do this? once? or repetitively, and if so, why?

3

Yes, I’ve got a primary key “ID” ( auto increment - starts from 1 ). I am filling table with data and then I want to get rid of records that dont fit in first 20 records, ordering by VALUE as mentioned above.

4

yes, i understand what you want to do

how often?

5

Once a day

6

okay, then you will need a separate table to store the ids in

(mysql will not let you delete from the same table you’re selecting from, so you have to do it in two steps)

DELETE FROM top_ids; – clears the table

INSERT INTO top_ids
SELECT id FROM stats
ORDER BY value_column LIMIT 0,20; – stores top 20 ids in top_ids table

DELETE FROM stats
WHERE id NOT IN ( SELECT id FROM top_ids ) – vwalah

7

Thanks Rudy, now we’ve got an issue and solution, hopefully someone will use it again.