simple sql query..help please

Dev Shed Forums Sponsor:

Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!

September 17th, 2004, 06:44 AM

help_me

Registered User

Join Date: Sep 2004

Posts: 1

Time spent in forums: < 1 sec
Reputation Power: 0

simple sql query..help please

Hey.. hope someone can help, i desperatly need some assistance. I have a few query issues but first things first.. one is that i have a table of messages and users having an online conversation, so fields are msg id, subject, topic, message, user, reply to and time it was sent. Im trying to do a query that will tell me which user sent the most messages, ive tried using count and stuff like that but not really getting anywhere. It will basically have to count each time a user has spoken and then give me the name of the user that has sent the most messages, ive been playing around with stuff like
SELECT Count(*) AS Expr1
FROM Table1
WHERE User='andro8472' OR User='bumies';

That will count the times those users have spoken but cant get further

any help would be greatly appreciated

really stuck at the mo..

thanks

September 17th, 2004, 05:05 PM

bocmaxima

Contributing User

Join Date: Aug 2004

Location: Tucson, Sonora

Posts: 1,322

Time spent in forums: 3 Days 17 h 46 m 5 sec
Reputation Power: 22

I'm not sure how your query is even working without a GROUP BY clause...it should be giving you a SQL error.
To get what you want, you need to add:
GROUP BY User
ORDER BY Expr1 DESC

to the end.

This will show you the number of records (messages) for each user, ordered highest to lowest recordcount (# of messages).

Hope that helps.

September 18th, 2004, 03:42 AM

Username=NULL

Contributing User

Join Date: Apr 2004

Location: TX

Posts: 249

Time spent in forums: 1 Day 7 h 6 m 42 sec
Reputation Power: 5

a Group By is only needed when the aggregate function is accompanied by other fields in the Select that aren't using aggregate functions.

Code:

select  min(value) from there

select  count(*) from there

select  min(value), count(*), max(otherValue) from there

are all valid w/out a group by.

September 18th, 2004, 04:22 AM

Username=NULL

Contributing User

Join Date: Apr 2004

Location: TX

Posts: 249

Time spent in forums: 1 Day 7 h 6 m 42 sec
Reputation Power: 5

I'm assuming this table...

Code:

tMessage
--------
MsgID
Subject
Topic
Message
User
ReplyTo
TimeSent

Give this a shot, as always my stuff is not guaranteed

. If it works then feel free to use it, but do you understand how it works? Something tells me there's a much easier way to do this...I'm sure Rudy or others will give some improvements if they see fit, it's 4am and I've been looking @ sql all day so I'm kinda just throwing out the 1st thing in my mind right now.

Code:


select	M.user, count(M.msgID) as CountMessages
from	tMessage M
group	by M.User
having	count(M.msgID) = 
	(		
	 select	max(A.cntMessage) as maxMsgSender  
	 from	( 
		 select	user, count(msgID) as cntMessage  
		 from	tMessage			  
		 group	by user
		) as A
	)

First get each user and a count of the messages they've sent thus far.

Then, get the max number of msg's sent, this will be used to compare which user has submitted the highest number of msg's.

Now again, we're getting each user and the num of msg's they've sent, only now (in the Having) we're only filtering on those that have a match for having sent the most msg's out of all the users.

Last edited by Username=NULL : September 18th, 2004 at 04:32 AM.

September 18th, 2004, 07:25 AM

r937

SQL Consultant

Dev Shed God 25th Plane (17000 - 17499 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,344

Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 26 m 34 sec
Reputation Power: 891

this works, but ignores ties:

Code:

select top 1 user, count(*) from messages
group by user 
order by count(*) desc

__________________
r937.com | rudy.ca

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: