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

my sqlite table has following format (all not null , not unique integer example):

time        type data 1436268660  0    ... 1436268661  1    ... 1436268662  0    ... 1436268666  2    ... 1436268668  1    ... 

sometimes need delete rows of each type older time need leave 5 newest each type if older specific time. in other words, leave 5 newest rows of each type , newer specified time (if there more 5) , delete rest.

so, if specified time x , type 0 has 20 rows newer x, nothing done type 0 (all new enough). if specified time x , type 0 has 5 rows older x, nothing done (there not more 5 of them). if there example 7 entries , @ least 2 of them older x, 2 oldest deleted.

what have far query. not correct. deletes rows older x when there more 5 of type. if older x nothing left.

delete table rowid in    (select table.rowid table join     (select type table group type having count(*) > 5)    using (type) time < 14362685399); 

as can see situation little bit more complicated "type" described above me in reality unique combination of multiple columns (you can replace type1,type2,type3), guess not important solution. thank 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     ... 

edit: need delete rows not in: (newer x) union (5 latest entries each type). i don't know how create result "5 latest entries each type".

try this. i've renamed table t.

delete t rowid in(   select a.rowid t   time < 14362685399   , a.rowid not in (     select b.rowid t b     a.type = b.type     order b.time desc     limit 5   ) ); 

note may not efficient on large data, due correlated subquery evaluated each time required (possibly once per distinct type in table, or maybe once per row in table, depending on how query executed).

as aside, in sql variant supports it, better achieved window function. example, in postgres.