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

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
– Abhik Chakraborty, Commented Apr 20, 2015 at 12:56

Sashi Kant · Accepted Answer · 2015-04-20 12:57:51Z

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, 2015 at 12:57

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:14:19Z

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);

CommunityBot

11 silver badge

answered Apr 20, 2015 at 13:00

Johan

892 silver badges15 bronze badges