Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » Rank records

Rank records

Author: rootsql Date: 1-11-2011, 22:34

Example shows how to rank records in a table.

CREATE TABLE actor(
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL
);
INSERT INTO actor VALUES 
  (1, 'PENELOPE', 'GUINESS'),
  (7, 'GRACE', 'MOSTEL'),
  (8, 'MATTHEW', 'JOHANSSON'),
  (10, 'CHRISTIAN', 'GABLE'),
  (11, 'ZERO', 'CAGE'),
  (20, 'LUCILLE', 'TRACY'),
  (21, 'KIRSTEN', 'PALTROW');

Output table with rank:

SET @i = 0;
SELECT id, first_name, last_name, @i := @i + 1 rank FROM actor ORDER BY id;
+----+------------+-----------+------+
| id | first_name | last_name | rank |
+----+------------+-----------+------+
|  1 | PENELOPE   | GUINESS   |    1 |
|  7 | GRACE      | MOSTEL    |    2 |
|  8 | MATTHEW    | JOHANSSON |    3 |
| 10 | CHRISTIAN  | GABLE     |    4 |
| 11 | ZERO       | CAGE      |    5 |
| 20 | LUCILLE    | TRACY     |    6 |
| 21 | KIRSTEN    | PALTROW   |    7 |
+----+------------+-----------+------+

Tags: Tips

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.