reversing ids in sql table

i have a table with primary id

1 simer

2 pawan

3 deep

4 sheep

5 man

i want reverse this order

5 simer

4 pawan

3 deep

2 sheep

1 man

how can i do this with programming or sql? i 开发者_如何转开发dont want to use order by in sql

Either:

1) Select all the rows ordered by id in descending order, then insert them into a new table in ascending order, and that table's AUTO_INCREMENT will assign the identifiers in reverse order. Now you can copy those rows back to the original table with their new identifiers.

2) Select all the rows ordered by id in descending order into a program, delete them, and reinsert them with the new IDs:

$sql = "SELECT id, name FROM table ORDER BY id DESC";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
    $rows[] = $row;
}

//empty the table, to avoid primary key conflicts
mysql_query("TRUNCATE TABLE table");    

$i = 1;
foreach ($rows as $row) {
  mysql_query("INSERT INTO table (id, name) VALUES ($i, '" . mysql_real_escape_string($row['name']) . ")");
  $i++;
}

i dont want to use order by in sql

But this is exactly the reason that SQL provides the ORDER BY clause.

Do you simply want to reverse the order of the current data or do you want your system ro assign decreasing sequence numbers to new records?

The former would be a pre-requisite for the latter so....

UPDATE yourtable SET id=(4294967295-id);

Note that while you can control the amount by which each new autoincrement value differs from the previous (by specifying the auto_increment_increment), simply setting this to -1 would not solve the problem as the base value this delta is applied to is SELECT MAX(id) FROM yourtable - so even if mysql allowed you to specify an auto_increment_increment of -1, you'd end up with duplicate rows, therefore you'd need to repliace all references to auto_increment types in your schema with straight integers and use a sequence generator instead. Then you'd need to rewrite all your code to use the sequence generator instead of INSERT_ID() / mysql_insert_id().

Wouldn't it be simpler to just use ORDER BY?