August 4, 2015 at 8:49 am
Hi Team,
I have a table 'library' with two columns
user_name, access_time
every user is having more than 500 entries, i want to keep only top 50 records for every user and delete the remaining records.
August 4, 2015 at 8:56 am
Minnu (8/4/2015)
Hi Team,I have a table 'library' with two columns
user_name, access_time
every user is having more than 500 entries, i want to keep only top 50 records for every user and delete the remaining records.
What does 'top' mean?
You've detailed your requirement: now what is your question?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 4, 2015 at 8:56 am
So how do you identify the records you want to save?
TOP 50 (with a cross apply).
then delete the rest... so either NOT IN ... or outer join...
August 4, 2015 at 8:59 am
pietlinden (8/4/2015)
So how do you identify the records you want to save?TOP 50 (with a cross apply).
then delete the rest... so either NOT IN ... or outer join...
That's not needed if you can delete directly using a CTE with ROW_NUMBER partitioned by user.
August 4, 2015 at 9:00 am
access_time field is the accessed time of user, want to keep the recent 50 entries and delete all the remaining for every user.
August 4, 2015 at 9:02 am
You've been here long enough to know that you should post DDL and sample data to get a coded solution.
August 4, 2015 at 9:26 am
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;
August 5, 2015 at 3:15 am
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.
August 5, 2015 at 5:16 am
how to use the same code for DB2, because i need the same query in DB2 database also.
I suggest you post this in a DB2 forum.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 5, 2015 at 5:26 am
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply