how to sort data and then number the rows in mysql?

Question

there's table with rows, for example:

+----+
|num |
+----+
| 6  |   
| 10 |
| 3  |
+----+

I try to sort descending and then number the rows

select num, @c:=@c+1 as c from table1 order by num desc;

but it's not exactly what I need. Is there any possibility to get table below?

+----+----+
|num |  c |
+----+----+
| 10 |  1 |
| 6  |  2 |
| 3  |  3 |
+----+----+

When you sort the data with `num` and there is no other values with the same `num` then sorting with row number will not make any difference. — Abhik Chakraborty, Apr 20 '15 at 12:56

score 0 · Accepted Answer · answered Apr 20 '15 at 12:57

0

Try this:

SET @rownum:=0;

SELECT *
FROM (SELECT @rownum:=@rownum+1 as rownum, num
             *
      FROM (Select num from table1 order by num desc))

answered Apr 20 '15 at 12:57

Sashi Kant

score 0 · Answer 2 · edited May 23 '17 at 12:14

0

You have to initialize the row count variabele:

SELECT num, @c := @c + 1 AS row_number FROM test_table JOIN (SELECT @c := 0) c ORDER BY num DESC;

Test set:

CREATE TABLE `test_table` (
  `num` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `test` (`num`) VALUES (6), (10), (3);

edited May 23 '17 at 12:14

Community

answered Apr 20 '15 at 13:00

Johan

2 Answers2