mardi 7 juillet 2015

sqlite query - select all older than X days, not Y newest

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