SQL Server Query for Rank (RowNumber) and Groupings

Use "Partition by" in the ranking function OVER clause

SELECT    Rank() over (Partition by Category Order by Value, User, Category) as ranks,    Category, UserFROM     Table1Group By    User, Category, Value Order by    ranks asc

 Select User, Category,     (Select Count(*) From Table       Where Category = A.Category          And Value <= A.Value) Rank From Table A Order By Category, Value

If Value can have duplicates, then you must decide whether you want to 'count' the dupes (equivilent to RANK) or not (equivilent to DENSE_RANK, thanx @shannon)

Ordinary Rank:

 Select User, Category,     (Select 1 + Count(*) From Table -- "1 +" gives 1-based rank,       Where Category = A.Category    -- take it out to get 0-based rank         And Value < A.Value) Rank From Table A Order By Category, Value 

"Dense" Rank:

 Select User, Category,     (Select 1 + Count(Distinct Value) -- "1 +" gives 1-based rank,       From Table                       -- take it out to get 0-based rank      Where Category = A.Category             And Value < A.Value) Rank From Table A Order By Category, Value