title: Optimize sql query with the rank function
body: <p>This query gets the top item in each group using the ranking function.</p>
  
  <p>I want to reduce the number of inner selects down to two instead of three. I tried using the rank() function in the innermost query, but couldn't get it working along with an aggregate function.  Then I couldn't use a where clause on 'itemrank' without wrapping it in yet another select statement.</p>
  
  <p>Any ideas?</p>
  
  <pre><code>select *
  from (
      select 
          tmp.*,
          rank() over (partition by tmp.slot order by slot, itemcount desc) as itemrank
      from (
          select
              i.name, 
              i.icon,
              ci.slot,
              count(i.itemid) as itemcount
          from items i
          inner join citems ci on ci.itemid = i.itemid
          group by i.name, i.icon, ci.slot    
      ) as tmp
  ) as popularitems
  where itemrank = 1
  </code></pre>
  
  <p>EDIT: using sql server 2008</p>