SQL Server Query for Rank (RowNumber) and Groupings

sql sql-server tsql group-by rank
bladefist picture bladefist · Jul 16, 2009 · Viewed 58.8k times · Source

I have a table that has some columns: User, Category, Value

And I want to make a query that will give me a ranking, of all the users by the value, but reset for the category.

Example:

user1   CategoryA 10
user2   CategoryA 11
user3   CategoryA 9
user4   CategoryB 3
user1   CategoryB 11

the query would return:

Rank  User   Category  
1     user2   CategoryA
2     user1   CategoryA
3     user3   CategoryA
1     user1   CategoryB
2     user4   CategoryB

Any ideas?

I write the query and specify the Category, It works but then I have to write loops and its very slow.

Answer

gbn picture gbn · Jul 16, 2009

Use "Partition by" in the ranking function OVER clause

SELECT
    Rank() over (Partition by Category Order by Value, User, Category) as ranks,
    Category, User
FROM 
    Table1
Group By
    User, Category, Value 
Order by
    ranks asc

Related questions

How to use group by with union in t-sql

How can I using group by with union in t-sql? I want to group by the first column of a result of union, I wrote the following sql but it doesn't work. I just don't know how to reference the …

Read More
How to return a incremental group number per group in SQL

I would like create a data query in SQL to incrementally number groups of rows, grouped on a common datetime and keep the "group numbers" incrementing on the next datetime and so on. These "group numbers" must not reset for …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More