Multiple TOP 10 PERCENT from same table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2008 Forums

Transact-SQL (2008)

Multiple TOP 10 PERCENT from same table?

Author

Topic

topazsparrow
Starting Member

7 Posts

Posted - 2011-08-23 : 16:42:45

So far I've got this. It works as it should and displays the correct fields and respective values. In this case it's querying all the devices on my network with top 10% of high latency values

SELECT TOP 10 percent
Nodes.HA AS HA,
Nodes.Site AS Site,
Nodes.WANbandwidth AS WANbandwidth,
AVG(ResponseTime.AvgResponseTime) AS AVERAGE_of_Average_Response_Time,
MIN(ResponseTime.MinResponseTime) AS MIN_of_Minimum_Response_Time

FROM 
Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)



WHERE
( DateTime BETWEEN 40723 AND 40753.9999884259 ) AND
(Nodes.WANnode = 1)
AND
(HA = 'VCHA')

GROUP BY Nodes.HA, Nodes.Site, Nodes.WANbandwidth

ORDER BY AVERAGE_of_Average_Response_Time desc

What I need is the same output for top 10% for each HA (one HA = FHA, one HA = PHSA)

When I combine them into one select statement it gives me the top 10% for all of them combined, and when I use multiple select statements with UNION it returns values that are not right (clearly not the top 10% of the given HA)

if anyone can provide some insight as to how I might accomplish this task, it would be greatly appreciated. I'm very new to SQL so go easy on me!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-23 : 17:36:12

maybe this:


select HA,
       Site,
       WANbandwidth,
       AVERAGE_of_Average_Response_Time,
       MIN_of_Minimum_Response_Time
from   (
       SELECT 
       Nodes.HA AS HA,
       Nodes.Site AS Site,
       Nodes.WANbandwidth AS WANbandwidth,
       AVG(ResponseTime.AvgResponseTime) AS AVERAGE_of_Average_Response_Time,
       MIN(ResponseTime.MinResponseTime) AS MIN_of_Minimum_Response_Time
       ,ntile(10) over (partition by nodes.HA order by AVG(ResponseTime.AvgResponseTime)) as pct
       FROM 
       Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)


       WHERE
       ( DateTime BETWEEN 40723 AND 40753.9999884259 ) AND
       (Nodes.WANnode = 1)
       AND
       (HA in ('VCHA', 'FHA', 'PHSA'))

       GROUP BY Nodes.HA, Nodes.Site, Nodes.WANbandwidth
) d
where  pct = 1
ORDER BY AVERAGE_of_Average_Response_Time desc

Be One with the Optimizer
TG

topazsparrow
Starting Member

7 Posts

Posted - 2011-08-23 : 17:46:47

Perfect! Thanks.

I only had to make one minor adjustment! Got lucky on my first try.
[CODE]
ntile(10) over (partition by nodes.HA order by AVG(ResponseTime.AvgResponseTime) ****DESC****) as pct

[/CODE]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-23 : 18:07:14

Great - glad it worked!

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources