How to sort values in columns and update table?

Question

I'm completely new to sql and can't do that myself. So I need your help. I want to sort values in column and then save changes. But I don't know how to do that.

Table looks like that:

Id | Name | SomeDescription
---------------------------
1  |Best  | Description1
2  |Worth | Description2
3  |Good  | Description3

I want to get something like that:

Id | Name | SomeDescription
---------------------------
1  |Best  | Description1
2  |Good  | Description3
3  |Worth | Description2

So I need to sort "id" and "name" columns.

I use following statement to sort values of "name" column:

SELECT * FROM games ORDER BY name ASC

But how can I sort the values of id column and save changes in table? Please, help.

Answer 1

You would have to use a second table

1. create a new table games2 with the same structure as your games table, making sure the ID is auto-incrementing

   CREATE TABLE `games2` LIKE `games`;
   

2. copy the data, sorted, into games2

   INSERT INTO `games2` (`Name`, `SomeDescription`) SELECT `Name`, `SomeDescription` FROM `games` ORDER BY `Name`
   

3. drop or move the old table

   -- DROP TABLE `games`;
   -- or
   RENAME TABLE `games` TO `games1`;
   

4. rename new table to old name

   RENAME TABLE `games2` TO `games`;
   

These steps will result in what you want.

Answer 2

You can use the ROW_NUMBER ranking function to renumber the rows.

SELECT UnsortedId = id
, SortedId = ROW_NUMBER() OVER (ORDER BY g.name, g.id)
FROM games