April 9, 2014 at 10:04 am
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.....
April 9, 2014 at 10:23 am
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.
April 9, 2014 at 10:34 am
Great! I did not know we could use With Ties with TOP Clause.
Thanks a Ton
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply