MySQL query to reorder field values?

Question

I have table like this:

===============
| rank | name |
===============
|    3 | john |
|    6 |  bob |
|   10 | alex |
|   11 | brad |
|   12 | matt |
|   34 | luke |
|  145 |  ben |
===============

(this table is an example. In reality my table consists of ~5000 rows of data).

Is there a query to reorder the rank values starting from 1 and going up so it ends up like this:

===============
| rank | name |
===============
|    1 | john |
|    2 |  bob |
|    3 | alex |
|    4 | brad |
|    5 | matt |
|    6 | luke |
|    7 |  ben |
===============

It would be preferable to do this in 1 or 2 queries, not 1 query for each row since my table has 5000+ rows.

EDIT: Sorry I wasn't clear. I am trying to UPDATE the values in the database.

WHY do you care how the entries are physically ordered in the DB? The view (including order) should be controlled at the display layer (in SQL's case: in the resultset of a query). — hannebaumsaway, Jun 11 '12 at 22:21
#1 it's out of my control. If my client asks for something, I do it (if it's possible). #2, in some cases, the rank # can be duplicated (multiple rows with the same rank), which can really mess up stuff on the frontend of the application. So reordering their values starting from 1 is important. — Jake Wilson, Jun 12 '12 at 15:03
Actually, [duplicate ranks can be handled by the query](http://stackoverflow.com/questions/10855462/detect-future-duplicate-values-while-iterating-through-mysql-results-in-php) from the front-end application. — hannebaumsaway, Jun 12 '12 at 16:43

score 4 · Answer 1 · answered Jun 11 '12 at 21:12

4

This is a little crude but will work in a pinch.

First order your table correctly just incase

ALTER TABLE tablename ORDER BY rank

Then drop the column

ALTER TABLE tablename DROP rank

Then add it again, with auto increment

ALTER TABLE tablename ADD COLUMN rank INT NOT NULL AUTO_INCREMENT FIRST

The auto increment will take care of numbering them in order, plus you don't have to loop through each row.

answered Jun 11 '12 at 21:12

Steve Robbins

13,672
12
76
124

NEAT!! Can't say anything else :-)) – Sebas Jun 12 '12 at 00:35
What exactly does the `ALTER TABLE ORDER BY` do? Does it set the default `ORDER BY` for when using a `SELECT` query? – Jake Wilson Jun 13 '12 at 16:29
This won't work in my case because the table has a `PRIMARY KEY id` field and I am only allowed to have one `AUTO_INCREMENT` field. – Jake Wilson Jun 13 '12 at 16:44
Yes, you may have to "break" the table temporarily to get this to work. Even harder if you have foreign keys (then again they would get messed up anyway). – Steve Robbins Jun 13 '12 at 17:32
I found a solution (posted and accepted) that is similar, involving creating a temporary table, populating it, adding the auto-increment column and then updating the original table with the new values. +1 to you for the auto-incrementing column idea though! The database is used in a high traffic website so breaking the table even momentarily could be a bad thing. – Jake Wilson Jun 13 '12 at 21:56

score 0 · Accepted Answer · answered Jun 13 '12 at 17:35

Here is the solution I came up with for this problem:

1.Create a temporary table without any keys

CREATE TEMPORARY TABLE tempTable (
  id INT(11) NOT NULL
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT;

2.Populate the temporary table with data from the original table, ordered by rank

INSERT INTO tempTable SELECT id FROM myTable ORDER BY rank;

3.Add auto-incrementing rank column, giving all rows a unique rank, counting up from 1

ALTER TABLE tempTable
    ADD COLUMN `rank` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    ADD PRIMARY KEY (`rank`);

4.Update the original table with a join to the temp table, overriding the original ranks

UPDATE myTable 
INNER JOIN tempTable
    ON myTable.id = tempTable.id
SET myTable.rank = tempTable.rank;

5.Drop the temp table

DROP TABLE tempTable;

Steve Robbins · Answer 3 · 2012-06-13T18:06:25.753

An alternative to a strict MySQL solution would be to loop through the rows with a scripting language. Not a great idea if you have a large table, but could be acceptable if this is a one time fix.

In PHP

$db = mysql_connect('localhost', 'user', 'password');
mysql_select_db('database', $db);

$result = mysql_query("SELECT rank
                        FROM myTable
                        ORDER BY rank");

$i = 1;

while ($row = mysql_fetch_assoc($result)) {

    mysql_query("UPDATE myTable
                SET rank = " . $i++ . "
                WHERE rank = " . $row['rank']);
}

Note that this will only work if rank is unique and you traverse in an order.

Appreciated but as I stated in my original question, my table is about 5000+ rows so running 5000+ queries on the server is something I wanted to avoid. Thanks though. — Jake Wilson, Jun 13 '12 at 21:55

amir nouri · Answer 4 · 2016-10-03T19:22:32.037

0

set @a:=(select max(id) from mytable)+1;
update mytable set id=(@a:=@a+1)
order by id;
set @a := 0;
update mytable set id=(@a:=@a+1)
order by id;

simple way, work for me. easy way.

edited Oct 03 '16 at 19:22

answered Oct 03 '16 at 18:59

amir nouri

9
5

MySQL query to reorder field values?

4 Answers4