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 Expand / Collapse
Author
Message
Posted Wednesday, April 9, 2014 10:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:52 AM
Points: 253, Visits: 761
I am trying to count the serversState based on server. Here is the query and Output

Select top 20 COUNT(State) StateCount ,Server_Name
FROM Production
Group By Server_Name
Order By COUNT(State ) Desc

StateCount Server_Name
11 abc
8 cde
5 JDE
.........

My Concern is I need to get top 20 But if the StateCount of 20 and 21st are same then get 21 row , If 21st and 22nd is same then get that too... Until all of the ones with same StateCount as of Count 20 are covered. I hope I am not confusing.....
Post #1560045
Posted Wednesday, April 9, 2014 10:23 AM This worked for the OP Answer marked as solution


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:02 PM
Points: 20,862, Visits: 32,893
Add WITH TIES to your select top 20. Hope the following helps illustrate what you need:


create table dbo.production(
ServerName varchar(20),
ServerState varchar(10)
);

insert into dbo.production
values ('ABC','A'),('ABC','B'),('ABC','C'),('ABC','D'),('ABC','E'),
('CDE','A'),('CDE','B'),('CDE','C'),('CDE','D'),('CDE','E'),
('FGH','A'),('FGH','B'),('FGH','C'),('FGH','D'),
('ZXC','A'),('ZXC','B'),('ZXC','C'),('ZXC','D'),('ZXC','E')

Select top 1 with ties COUNT(ServerState) StateCount ,ServerName
FROM production
Group By ServerName
Order By COUNT(ServerState ) Desc;

drop table dbo.production;


Please note that I was running this in a sandbox database and clean up after using the table.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1560058
Posted Wednesday, April 9, 2014 10:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:52 AM
Points: 253, Visits: 761
Great! I did not know we could use With Ties with TOP Clause.
Thanks a Ton
Post #1560065
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse