Hi

I have the following query that returns the top 10 products in each category basead on the revenue of the product. But I'd like to have the top 50% of the number of the products in each category. Any Ideas?

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