Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Count of Max Servers Expand / Collapse
Author
Message
Posted Friday, April 4, 2014 10:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:09 PM
Points: 215, Visits: 636
I am trying to get top 20 servers where the servername Count is maximum.

Here is my Query ...
Select Count(Server_Name)ServerCount , Server_Name from Temp1 A
Group By Server_Name
Having COUNT(Server_Name) = (Select MAX(CNT) from (Select Count(Server_Name)as CNT , Server_Name from Temp1 group By Server_Name )A

I can get only one server name which has the maximum # of servers.
I tried top 20 but its not working .. Any ideas?


The Table has this Column called Server Name such as
ABC1111
ABC222
ABC222
ABC222
ABC222
XYZ111

So , I need to get the servername Counts which repeat multiple times and get the max of those for top 20.
(Hope I haven't made it confusing)
Post #1558595
Posted Friday, April 4, 2014 12:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 13,083, Visits: 11,918
It would be REALLY helpful if you could provide ddl and sample with your posts.

I am not 100% certain what you are trying to do here but see if this does it.

Select Count(Server_Name)ServerCount , Server_Name 
from Temp1 A
Group By Server_Name
order by Count(Server_Name)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1558610
Posted Monday, April 7, 2014 5:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 10:05 PM
Points: 50, Visits: 127
select top 20
Servername, count(*)
from tablename
group by servername order by 2


Hope this answers your problem
Post #1559010
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse