Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » Rank records (grouped ranking)

Rank records (grouped ranking)

Author: rootsql Date: 7-11-2011, 22:09

Example shows how to perform grouped ranking in MySQL.

CREATE TABLE records(
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  member_id INT(11)
);
INSERT INTO records VALUES
  (1, 125),
  (2, 125),
  (3, 340),
  (4, 340),
  (5, 340);

 

Variant 1:

SET @i = 0;
SET @member_id = NULL;
SELECT
  id, member_id, rank
FROM (
  SELECT id, member_id, IF(@member_id = member_id, @i := @i + 1, @i := 1) rank, @member_id := member_id
  FROM records
  ORDER BY member_id, id
) t;

Variant 2:

SELECT t1.id, t1.member_id, COUNT(*) rank FROM records t1
  LEFT JOIN records t2
    ON t2.id <= t1.id AND t2.member_id = t1.member_id
GROUP BY
  t1.id;

Output:

+----+-----------+------+
| id | member_id | rank |
+----+-----------+------+
|  1 |       125 |    1 |
|  2 |       125 |    2 |
|  3 |       340 |    1 |
|  4 |       340 |    2 |
|  5 |       340 |    3 |
+----+-----------+------+
Dear visitor, you went to the site as unregistered user.
We recommend you to Register or Sign In.

Comments:

Leave a comment
Information
Members of Guests cannot leave comments.