 <p>Without CTEs you can do:</p> <pre><code>Select Z.Department, Z.AvgWage From ( Select Department, Avg(Wage) AvgWage From Employees Group By Department ) As Z Where AvgWage = ( Select Max(Z1.AvgWage) From ( Select Department, Avg(Wage) AvgWage From Employees Group By Department ) Z1 ) </code></pre> <p>With CTEs you could do:</p> <pre><code>With AvgWages As ( Select Department , Avg(Wage) AvgWage , Rank() Over( Order By Avg(Wage) Desc ) WageRank From Employees Group By Department ) Select Department, AvgWage, WageRank From AvgWages Where WageRank = 1 </code></pre>