<div>
select a.* , top5 = row_number() over(order by percentcomp desc), bottom5 = row_number() over(order by percentcomp asc)<br />
from <br />
(<br />
SELECT &nbsp; &nbsp; Regional_Location.Country,<wbr /> 100 * SUM(CASE WHEN pse.LastStateName = 'Install Verified' THEN 1 ELSE 0 END) / COUNT(*) AS 'percentcomp'<br />
FROM &nbsp; &nbsp; &nbsp; &nbsp; v_GS_PatchStatusEx AS pse INNER JOIN<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; v_R_System ON pse.ResourceID = v_R_System.ResourceID INNER JOIN<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Regional_Location ON v_R_System.AD_Site_Name0 = Regional_Location.AdSite INNER JOIN<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; v_ApplicableUpdatesSummary<wbr />Ex ON pse.UpdateID = v_ApplicableUpdatesSummary<wbr />Ex.UpdateI<wbr />D<br />
WHERE &nbsp; &nbsp; (v_ApplicableUpdatesSummar<wbr />yEx.TimeAu<wbr />thorized not like 'Null')<br />
GROUP BY Regional_Location.Country<br />
) a <br />
where<br />
top5 &lt;= 5 or <br />
bottom5 &lt;= 5<br />

</div>


<div>
Wow. That was fast. Thanks a lot for the help. 
</div>


<div>
Thanks a lot. I really appreciate it.
</div>


<div>
<div class="content wysiwyg-content">
Hello. I am trying to modify the SQL query below to show the Top 5 and Bottom 5 results. I have a country table. I want to display the top 5 countries in % compliance and the bottom 5 % compliance. <br />
<br />
I tried several ways of using Top (5) but could not get it to work. Any suggestions?<br />

<pre><code class="code-1 nolinks" id="code-124543"><span>SELECT     Regional_Location.Country, 100 * SUM(CASE WHEN pse.LastStateName = 'Install Verified' THEN 1 ELSE 0 END) / COUNT(*) AS '% Compliant'</span>
<span>FROM         v_GS_PatchStatusEx AS pse INNER JOIN</span>
<span>                      v_R_System ON pse.ResourceID = v_R_System.ResourceID INNER JOIN</span>
<span>                      Regional_Location ON v_R_System.AD_Site_Name0 = Regional_Location.AdSite INNER JOIN</span>
<span>                      v_ApplicableUpdatesSummaryEx ON pse.UpdateID = v_ApplicableUpdatesSummaryEx.UpdateID</span>
<span>WHERE     (v_ApplicableUpdatesSummaryEx.TimeAuthorized not like 'Null')</span>
<span>GROUP BY Regional_Location.Country</span>
</code></pre>
</div>
</div>


Hello. I am trying to modify the SQL query below to show the Top 5 and Bottom 5 results. I have a country table. I want to display the top 5 countries in % compliance and the bottom 5 % compliance. 

I tried several ways of using Top (5) but could not get it to work. Any suggestions?

Display Top 5 using MS SQL 2005

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.