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.
| Author |
Topic |
|
TheKai
Starting Member
16 Posts |
 Posted - 2010-04-14 : 23:24:10
|
| Sorry for the wonky subject, but I'm not sure how to summarize this.I have a table with messages. TABLE [dbo].[tblInbox]( [ID] [int] IDENTITY(1,1) NOT NULL, [FromID] [int] NOT NULL, [ToID] [int] NOT NULL, [SendDateTime] [datetime] NOT NULLThe [ToID] field describes the recipient. I'm trying to fix it so that no recipient has more than 100 messages in the inbox. Should there be more than 100 message for a recipient, I want to cull the oldest messages.Does that make sense at all?I've been searching high and low for info on how to write that a proc that would allow me to do this.Somehow, I thought this would work:Delete from tblInbox where ID in (Select ID from tblInboxwhere COUNT(ToID) > 100) |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-04-14 : 23:39:50
|
[code]delete dfrom ( select *, row_no = row_number() over (partition by ToID order by SendDateTime desc) from tblInbox ) dwhere row_no > 100[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
TheKai
Starting Member
16 Posts |
Posted - 2010-04-15 : 01:11:13
|
| Your suggestion works Perfect. Thanks a ton! |
|
|
|
|
|
|
Delete records until only X records remain?