renumber items in a row selection

Question

I have a number of rows in a selection with a sort index like

ID   text  date         sort_index
43   ABC   2013-05-28   3
93   DEF   2013-05-28   14
12   ABC   2013-05-28   103
[...]

Now I would like to renumber the sort_index from 0 to 2. The group element is date. How can I do that by using MySQL?

You mean you want sort_index to be the ID of the row of the results? — Menelaos, May 29 '13 at 15:53
You might find this question useful: http://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by — Khior, May 29 '13 at 15:55
Also, do you wish to update your actual table based on the results of the query? — Menelaos, May 29 '13 at 15:56

score 2 · Answer 1 · answered May 29 '13 at 16:25

2

Try this, but not tested for every case yet.

UPDATE tbltest SET sort_index =
(
    SELECT COUNT(*) FROM (
        SELECT * FROM tbltest
    ) AS dup
    WHERE  dup.`date` = tbltest.`date` AND
           dup.sort_index < tbltest.sort_index
)

answered May 29 '13 at 16:25

invisal

11,075
4
33
54

bitWorking · Answer 2 · 2014-09-08T23:43:04.753

1

Check out this site.

In your case it would go like this:

mysql> SET @ordering_inc = 1;
mysql> SET @new_ordering = -1;
mysql> UPDATE YOUR_TABLE SET 
       sort_index = (@new_ordering := @new_ordering + @ordering_inc)
       WHERE date = '2013-05-28'
       ORDER BY sort_index ASC;

Not tested!

edited Sep 08 '14 at 23:43

answered May 29 '13 at 16:10

bitWorking

12,485
1
32
38

Yep..this does the trick! If I hadn't reached my voting limit I'd +1 – Menelaos May 29 '13 at 16:18
I believe this only work for one specific date, while OP want a single query that works with many date inside the table. – invisal May 29 '13 at 16:29
You can always remove WHERE date = '2013-05-28' , which means you can easily make it work for all dates. – Menelaos May 29 '13 at 17:36
1

Of course, it will work differently when remove WHERE, but not as OP intended. – invisal May 29 '13 at 17:42

renumber items in a row selection

2 Answers2