Minnu (8/4/2015)
access_time field is the accessed time of user, want to keep the recent 50 entries and delete all the remaining for every user.
with base as (
select
*,
rn = row_number() over (partition by user_name order by access_time desc)
from dbo.library
)
delete from base where rn > 50;