This query gets the top item in each group using the ranking function.
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.
Any ideas?
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
EDIT: using sql server 2008