help with simple SP - 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 with simple SP

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-04-14 : 16:16:32

Hi, just wondering if I can grab a hand on this SP. I think its fairly simple but group by clauses always confuse me, I think thats what I need anyways.

I a table named tblMessages. It has the following rows

MessageID
MessageFromId
MessageToID
Message (varchar 500)
Date

I want to select the count of the top message senders. So basically I need a top 20 count of the most number of rows of messagefromID. I hope this is clear its a bit hard to explain.

The reason I am doing this is to see if there is any abuse of the system (spam, etc).

Also is there a simple way to detect a count of messages with exactly the same message field? So i know if a user is entering SPAM to other members of the site?

Thanks alot

Mike123

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-14 : 16:28:46

select top 20 MessageFromId, count(*)
from tbl
group by MessageFromId
order by count(*) desc

this will not give ties.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-15 : 03:39:16

nr has answered part 1 of your question, but you could use the same concept to find the most oft-repeated messages:

SELECT TOP 10 Message, COUNT(*)
FROM tblMessage
GROUP BY Message
ORDER BY COUNT(*) DESC

To find out who's been sending these messages, wrap the query into a derived table (Warning: this is not going to be fast!!):

SELECT MessageFromID, M.Message FROM
(
SELECT TOP 10 Message, COUNT(*)
FROM tblMessage
GROUP BY Message
ORDER BY COUNT(*) DESC) M
INNER JOIN tblMessage ON M.Message = tblMessage.Message

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources