SET @lddataini='20081121 00:00:00' SET @lddatafim='20081127 23:59:59'; with ReceitaporProduto(ProdID,Revenue,AmountSold) as ( SELECT PRODID,SUM(TOTAL) as revenue,SUM(ri.qtd) asAmountSold from romaneioitens ri inner join romaneio r on r.id=ri.romaneioID where r.caddate BETWEEN @lddataini and @lddatafim GROUP BY prodid ) select HRRanks.* from ( select prodId,categoryID,unestoque,Revenue,AmountSold, rank() over (partition by p.categoryID order by RP.revenue desc) as HR_Rank from ReceitaporProduto RP inner join produto p on p.id=RP.prodid inner join estoquefisico e on e.id=p.id where e.ativo=1 ) HRRanks where HR_Rank<=10 order by categoryID,HR_Rank