Query to get grouped items ranked?

Question

I have a table of hits for various users:

HITS:
id | userid
1  | 1     
2  | 1     
3  | 2     
4  | 1     
5  | 2     
6  | 2

I want the fastest possible way to get a list of these items ranked by ID. So this:

HITS RANKED:
id | userid | ranks
1  | 1      | 1
2  | 1      | 2
3  | 2      | 1
4  | 1      | 3
5  | 2      | 2
6  | 2      | 3

I want to avoid joining two tables to each other, as this takes forever when the tables get big. Any other suggestions?

I think SORT ORDER might make more sense? Basically, hit with ID 1 was first for user 1, id 2 was second for user 1, but only id 4 was third for user 3? — supercoder, Feb 27 '13 at 08:57
Duplicate: http://stackoverflow.com/questions/532878/how-to-perform-grouped-ranking-in-mysql — bonCodigo, Feb 27 '13 at 09:19

score 1 · Accepted Answer · answered Feb 27 '13 at 08:58

SELECT  ID, 
        UserID, 
        Ranks
FROM
    (
        SELECT  id,
                userid,
                @group:=CASE WHEN @temp <> userid THEN 1 ELSE @group+1 END AS ranks,
                @temp:=userid AS clset
        FROM    (SELECT @group:= 0) s,
                (SELECT @temp:= 0) c,
                (SELECT * FROM hits ORDER BY userid, id) t
    ) x
ORDER   BY ID

SQLFiddle Demo

Query to get grouped items ranked?

1 Answers1