is it possible to store the rank of ordered items rather then their score?

Question

For example I have this query which orders val2 according to their item with max count.

INSERT INTO table d (val1, val2 ,cnt)
SELECT val1, val2 , max(cnt) as cnt
FROM table2 WHERE val1 =x  GROUP BY
val2 ORDER BY max(cnt) DESC LIMIT 10;

Instead I want to rank the results so cnt isnt going to be a random number but 1 if its respective value in table 2 has the highest count, 2 if its the 2nd highest etc.

Answer 1

I would avoid storing the position (rank, order, whatever) of a row.

Reasoning:

• INSERTs will require the renumbering of the entire table in the worst case
• DELETEs will require the same
• UPDATEs to the cnt field could require the same

I would suggest using the following as a guideline for these type of queries:

SET @i := 0;
SELECT * FROM (
  SELECT @i := @i+1 AS rank, 
  ... 
  FROM table
  ORDER BY ...
) t;

Specifically, for your query:

SET @i := 0;
SELECT
  @i := @i + 1 AS rank,
  val1,
  val2,
  cnt
FROM (
  SELECT
    val1,
    val2,
    MAX(cnt) AS cnt
  FROM table2
  WHERE val1 = ?
  GROUP BY val2
  ORDER BY cnt DESC
) t
LIMIT 10;

Answer 2

I have to agree with hobodave that it would probably be better to rank as you fetch instead of as you insert, so you should reconsider if you really want to do this. But if this is actually what you need, it can be done as you requested like this:

INSERT INTO Table1 (val1, val2, cnt)
SELECT val1, val2, @rn := @rn + 1 AS rn
FROM (
  SELECT val1, val2, MAX(cnt) AS cnt
  FROM Table2
  WHERE val1 = 1
  GROUP BY val2
  ORDER BY cnt DESC
  LIMIT 10
) AS T1, (SELECT @rn := 0) AS vars;

SELECT *
FROM Table1
ORDER BY cnt;

Result:

1, 4, 1
1, 2, 2
1, 7, 3
1, 6, 4

Test data:

CREATE TABLE Table1 (val1 INT NOT NULL, val2 INT NOT NULL, cnt INT NOT NULL);
CREATE TABLE Table2 (val1 INT NOT NULL, val2 INT NOT NULL, cnt INT NOT NULL);
INSERT INTO Table2 (val1, val2, cnt) VALUES
(1, 2, 3),
(1, 4, 5),
(1, 4, 2),
(1, 6, 0),
(1, 7, 1),
(1, 7, 2),
(2, 1, 1);