SQL query using aggregate function

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

General SQL Server Forums

New to SQL Server Programming

SQL query using aggregate function

Author

Topic

lazyme
Starting Member

8 Posts

Posted - 2009-10-08 : 11:34:21

I have a table with Subscription information consists of subscription id, subscriber user id, subscribed user id and block flag as below


subscription_id    subscriber_user_id	subcribed_user_id	block

sub1	            u8	                u24		        F
sub2	            u25	                u26			F
sub3	            u26	                u25			F
sub4	            u16	                u15			F
sub5	            u5	                u13			F
sub6	            u10	                u13			F
sub7	            u6	                u10			F
sub8	            u8	                u9			T
sub9	            u2	                u24			F
sub10	            u3	                u24			T
sub11	            u19	                u20			F
sub12	            u18	                u21			F
sub13	            u27	                u26			T

If one user blocks the other user, the block flag is set as true.
I want to find a user who has the most non-blocking subscribers. How would I write a query for that?

This is what I tried


select max(S.subscriber_user_id)
from Subscription S where S.Subscriber_user_id IN
(Select S.Subscriber_user_id, S.block from Subscription S
where S.block = 'F');

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-08 : 12:14:09

SELECT TOP (1) WITH TIES Subscriber_User_ID
FROM Subscription
GROUP BY Subscriber_User_ID
ORDER BY SUM(CASE WHEN Block = 'F' THEN 1 ELSE 0 END) DESC

N 56°04'39.26"
E 12°55'05.63"