sql - Limit to 10 latest rows for each value of a given column -

sql - Limit to 10 latest rows for each value of a given column -

this question has reply here:

keep lastly 5 search results of user in table 1 reply

assume have next table structure:

create table foo.bar( id serial primary key, nema character varying(128) not null, cat int not null default 0, _date timestamp default default current_timestamp );

i fill table every day lot of data, , maintain latest 10 records of same cat value. illustration have 5k rows 100 of them have cat value 0xa007, maintain latest 10 records , delete rest 90 rows, following:

delete foo.bar f f.cat=(x'a007'::integer) , f.id not in ( select b.id foo.bar b b.cat=(x'a007'::integer) order b._date desc limit 10 offset 0)

but above works 1 category, how may have query categories (all values)?

you can like:

delete foo.bar id in ( select id ( select id, rank() on (partition cat order _date ) ranking foo.bar) ranking > 10 )

sql postgresql sql-delete