my sqlite table has following format:
time type data
1436268660 0 ...
1436268661 1 ...
1436268662 0 ...
1436268666 2 ...
1436268668 1 ...
Sometimes I need to delete all rows of each type which are older than some time but I need to leave 5 newest from each type even if they are older than that specific time. In other words, to leave 5 newest rows of each type and also all newer than specified time (if there is more than 5) and to delete the rest.
So, if the specified time is X and type 0 has 20 rows newer than X, nothing is done for type 0 (all are new enough). Also if the specified time is X and type 0 has 5 rows all older than X, nothing is done (there is not more than 5 of them). But if there is for example 7 entries and at least 2 of them are older than X, then those 2 oldest are deleted.
What I have so far is this query. But it is not correct. It just deletes all rows older than X when there is more than 5 of that type. If they are all older than X nothing is left.
DELETE FROM table WHERE rowid IN
(SELECT table.rowid FROM table JOIN
(SELECT type FROM table GROUP BY type HAVING COUNT(*) > 5)
USING (type) WHERE TIME < 14362685399);
As you can see the situation is little bit more complicated as "type" described above by me is in reality unique combination of multiple columns (you can replace by type1,type2,type3), but I guess it is not so important for the solution. Thank you for any help.
time type0 type1 type2 data
1436268660 0 0 0 ...
1436268661 1 1 1 ...
1436268662 0 0 0 ...
1436268666 2 2 2 ...
1436268668 1 1 1 ...
Aucun commentaire:
Enregistrer un commentaire