Minnu (8/5/2015)
Hi Team,Below code is working.
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;
how to use the same code for DB2, because i need the same query in DB2 database also.
IIRC, in DB2 I think you can do this: -
DELETE FROM (SELECT ROW_NUMBER() OVER ( PARTITION BY user_name ORDER BY access_time DESC ) FROM dbo.library) base(RN)
WHERE RN > 50;
But that won't work in SQL Server.