Mysql Query for Rank (RowNumber) and Groupings

Question

i have the similar problem mentioned in this thead

but it's in SQL server and Mysql doesn't support "Partition By" as i know now what can i do? Here is the Question:

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?

Not sure that whether I see what you're trying to do, take a look at `GROUP BY` — Jack, Apr 28 '14 at 15:21

Ravinder Reddy · Answer 1 · 2014-04-29T18:12:32.873

1

Edit 2: Based on OP's comment:

it worked with just a little bit wrong ranking and that's in the first rank.when similar categories with same value has the top rank one of them is the first in the rank and other is 2nd but they must have the first rank (1)

Following change is suggested:

select rank, user, category, value
from (
  select user, @cc:=category category, @cv:=value value
    , case when @pc=@cc and @pv=@cv then @rn:=@rn
           when @pc=@cc and @pv!=@cv then @rn:=(@rn+1)
           else @rn:=1
      end as rank
    , @pc:=@cc as temp_currCat
    , @pv:=@cv as temp_currVal
  from user_category_values,
       (select @pc:='', @cc:='', 
               @pv:='', @cv:='', 
               @rn:=0) row_nums
  order by category asc, value desc
) results;

Demo @ MySQL 5.5.32 Fiddle

edited Apr 29 '14 at 18:12

answered Apr 28 '14 at 15:34

Ravinder Reddy

23,692
6
52
82

Ravinder and Gordon Linoff thanx for your quick reply.i tested Ravinder's it run perfect but the problem is that in the same category with the same value it has increasing rank instead of same ranknig . Gordon Linoff i just tested your code in fiddle and it had errors – vahidasadi Apr 28 '14 at 20:02
it's very nice of you and thanx for help.it worked with just a little bit wrong ranking and that's in the first rank.when similar categories with same value has the top rank one of them is the first in the rank and other is 2nd but they must have the first rank (1) – vahidasadi Apr 29 '14 at 16:07
@vahidasadi: You should decide the requirements at the time of posting the question. Not after someone posts an answer based on the info given in the posting but not reading from the comments. – Ravinder Reddy Apr 29 '14 at 16:15

score 0 · Answer 2 · answered Apr 28 '14 at 15:26

You can use variables for this:

select rank, user, category
from (select t.*,
             @rank := if(@cat = category, @rank + 1, 1) as rank,
             @cat := category
      from table t cross join
           (select @cat := '', @rank := 0) const
     ) t
order by category, rank;

Mysql Query for Rank (RowNumber) and Groupings

2 Answers2

Linked