rank users based on count num

Question

how to rank users based on column num which is count of number of times user exists in table.Also if 2 users have same count it should give the user on top rank lesser than below eg: user 1 and 2 has count 3 then user 1 is rank 1 and user 2 is rank 3 and if there is new user 3 with count 1 then user 3 is rank 3 .

How do I do that? I really appreciate any help.Thank in Advance.

http://sqlfiddle.com/#!2/a9188/2

CREATE TABLE if not exists tblA
(
id int(11) NOT NULL auto_increment ,
user varchar(255),
 category int(255),
 PRIMARY KEY (id)
);


INSERT INTO tblA (user, category ) VALUES
('1', '1'),
('1', '2'),
('1', '3'),
('1', '1'),
('2', '1'),
('2', '1'),
('2', '1'),
('2', '1'),
('3', '1'),
('2', '1');

Response like: Search for category where its '1'

user  category    count    rank

1       1         2        1
2       1         2        2

query used:

SELECT USER,
       category,
       count(*) AS num
FROM tblA
WHERE category=1
GROUP BY USER,
         category
ORDER BY num DESC;

Take a look at this post, http://stackoverflow.com/questions/3333665/mysql-rank-function, and see if it helps. — Tim Burch, Feb 19 '14 at 14:06

score 2 · Accepted Answer · answered Feb 19 '14 at 14:08

2

For example, using subquery:

SELECT
  groups.*,
  @rank:=@rank+1 AS rank
FROM
  (select 
    user,
    category,
    count(*) as num
  from 
    tblA
  where 
    category=1 
  group by 
    user, 
    category
  order by 
    num desc,
    user) AS groups
  CROSS JOIN (SELECT @rank:=0) AS init

-check your modified demo.

answered Feb 19 '14 at 14:08

Alma Do

37,009
9
76
105

Firstly,Thanks for the query . I have an addon query http://sqlfiddle.com/#!2/305d2/10 to join user to userid to get usernames. – jason Feb 19 '14 at 14:24

rank users based on count num

1 Answers1