help complicated query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help complicated query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-11-10 : 05:09:19

Hi,

I've been trying to figure this one out for quite some time, and I'm not really sure if theres a decent way to get something like this to work.

I have two tables

tblUserDetails (userID)
tblMessages (messageID,userID,date)

What I want to accomplish is a DELETE statement that deletes messages from "tblMessages". Each user can have many messages in this table, I want to preserve the newest 500 for each user, and delete all other ones.

This seems like a pretty complex query. Does anybody have any suggestions on how to approach this ?

Thanks very much,
Mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 05:22:26

Try this

-- stage data
declare @stage table (rowid int identity(1, 1), userid int, messageid int)

insert		@stage
		(
			userid,
			messageid
		)
select		userid,
		messageid
from		tblmessages
order by	userid,
		[date] desc

-- do the work
delete		m
from		tblmessages m
inner join	(
			select		userid,
					min(rowid) mowid,
					499 + min(rowid) mawid
			from		@stage
			group by	userid
		) q on q.userid = m.userid and m.messageid not between q.mowid and q.mawid

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-11-10 : 07:48:52

thanks for the help once again peter :).. I moved this to a development server and tried it out.. took quite awhile to execute the code....... I had to add in a top 1000000 on the SELECT statement because the transaction log kept filling up.. I don't this would cause the query to produce the wrong results?

It currently does not seem to be preserving the first 500 rows. A few tested users ended up with 0 messages instead of the minimum 500 I wanted to keep. Any idea what it could be?

much appreciated,
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 08:36:20

Try this

-- do the work
delete		m
from		tblmessages m
left join	(
			select		userid,
					min(rowid) mowid,
					499 + min(rowid) mawid
			from		@stage
			group by	userid
		) q on q.userid = m.userid and m.messageid between q.mowid and q.mawid
where		q.userid is null

Try also to replace delete with select first to see that the wanted rows is displayed.
When you are sure that the rigth rows are displayed, put delete back again.

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-10 : 17:35:57

Here's a way to do it without the stage table. I doubt the performance will be better because it sounds like your performance problems have to do with the amount of rows being deleted and the resulting transaction log growth.

You probably need to run this or Peter's code (test both for performance) on single users or a range of users at a time so that you get smaller numbers of rows in each transaction.

You just need to run the delete query on your data with the second top set to 500 instead of 5.

declare @users table (UserId int)
declare @messages table (UserId int, MsgDate datetime)
insert @users values (1)
insert @users values (2)
insert @messages values (1, '1/1/2006')
insert @messages values (1, '1/2/2006')
insert @messages values (1, '1/3/2006')
insert @messages values (1, '1/4/2006')
insert @messages values (1, '1/5/2006')
insert @messages values (1, '1/6/2006')
insert @messages values (1, '1/7/2006')
insert @messages values (1, '1/8/2006')
insert @messages values (1, '1/9/2006')
insert @messages values (1, '1/10/2006')
insert @messages values (1, '1/11/2006')
insert @messages values (2, '2/1/2006')
insert @messages values (2, '2/2/2006')
insert @messages values (2, '2/3/2006')
insert @messages values (2, '2/4/2006')
insert @messages values (2, '2/5/2006')
insert @messages values (2, '2/6/2006')
insert @messages values (2, '2/7/2006')
insert @messages values (2, '2/8/2006')
insert @messages values (2, '2/9/2006')
insert @messages values (2, '2/10/2006')
insert @messages values (2, '2/11/2006')

--The delete query
delete @messages
from @messages md
inner join
(select u.UserId,
	(select top 1 a.MsgDate 
	from (select top 5 m.MsgDate from @messages m where m.UserId = u.UserId order by m.MsgDate desc) a
	order by a.MsgDate) MsgDate
from @users u) d on d.UserId = md.UserId and md.MsgDate < d.MsgDate 
--where md.UserId = 2

select * from @messages

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources