Make query to copy everything from one table to another one ORDERING BY

Question

I'm curious how to create table2 of the same structure with the same data as table1, but with order by the column frequency.

Or, the equivalent of this problem is: to change the id of rows in the table properly.

It doesn't matter, whether by ASC, or DESC.

As result, the table1:

**id - name - frequency**

    1 - John - 33
    2 - Paul - 127
    3 - Andy - 74

Should become table2:

**id - name - frequency**

    1 - Paul - 127
    2 - Andy - 74
    3 - John - 33

What's the shortest way to do that?

Also, I would be interesting in the query that's fastest for huge tables (although performance is not so important for me).

Nevermind. `SELECT INTO` is not available for mysql. http://www.w3schools.com/sql/sql_select_into.asp — David Starkey, Apr 01 '13 at 22:10

score 2 · Answer 1 · edited May 23 '17 at 12:03

2

Like this?

CREATE TABLE b SELECT col FROM a ORDER BY col

Be aware, there is no way to guarantee row order in a database (other than physically). You must always use an ORDER BY.

Reference

edited May 23 '17 at 12:03

Community

1
1

answered Apr 01 '13 at 20:04

Kermit

33,206
11
83
119

Seems like there is no id column for that table. – Haradzieniec Apr 01 '13 at 20:27

score 1 · Accepted Answer · answered Apr 01 '13 at 20:09

1

For this, you need to create the new id. Here is a MySQL way to do it:

create table table2 as
    select @rn := @rn + 1 as id, name, frequency
    from table1 cross join (select @rn := 0) const
    order by frequency desc

answered Apr 01 '13 at 20:09

Gordon Linoff

1,198,228
53
572
709

Make query to copy everything from one table to another one ORDERING BY

2 Answers2