try this approach also:Declare @myTable table (cat varchar(100), subcat varchar(100), rank int )Insert Into @myTable Select 'A', 'a', 2 Union All Select 'A', 'b', 52 Union All Select 'A', 'c', 14 Union All Select 'A', 'd', 46Union All Select 'A', 'e', 37 Union All Select 'A', 'f', 95 Union All Select 'A', 'g', 73 Union All Select 'A', 'h', 67 Union All Select 'A', 'i', 80 Union All Select 'A', 'j', 03 Union All Select 'B', 'a', 18 Union All Select 'B', 'b', 44 Union All Select 'B', 'c', 52 Union All Select 'B', 'd', 60 Union All Select 'B', 'e', 28 Union All Select 'B', 'f', 06 Union All Select 'B', 'g', 70 Union All Select 'B', 'h', 90 Union All Select 'B', 'i', 89 Union All Select 'B', 'j', 31declare @n intSet @n = 500Select Cat, subCat, rankFrom @myTable as AWhere (Select count(*) From @myTable Where cat=A.cat and rank <= A.rank)<=@nOrder By Cat, Rank Desc
but selecting first 500 per category won't be very fast either way.Go with the flow & have fun! Else fight the flow 
Select top X items for each category
Posted - 2005-06-28 : 11:51:47
The category/item table contains 41347 records.The way I understand correlated subqueries is that for each row returned by the outer query, the inner query gets executed. That means that I am executing the inner query (select top 500 ...) 41,347 times!Is there a better way to do this? Otherwise I may just use cursors and select all distinct categories and then for each category select the top 500 items in that category.
MadhivananFailing to plan is Planning to fail