Sort an entire SQLite table

Question

I have an SQLite table that I need to sort. I am familiar with the ORDER BY command but this is not what I am trying to accomplish. I need the entire table sorted within the database.

Explanation:

My table uses a column called rowed which sets the order of the table (a key?). I need to sort the table by another column called name and then re-assign rowid numbers in alphabetical order according to name. Can this be done?

Answer 1

Assuming you created your original table like so:

CREATE TABLE my_table (rowid INTEGER PRIMARY KEY, name TEXT, somedata TEXT) ;

You can create another sorted table like so:

CREATE TABLE my_ordered_table (rowid INTEGER PRIMARY KEY, name TEXT, somedata TEXT) ;
INSERT INTO my_ordered_table (name, somedata) SELECT name,somedata FROM my_table 
ORDER BY name ;

And if you then want to replace the original table:

DROP TABLE my_table ;
ALTER TABLE my_ordered_table RENAME TO my_table;

Answer 2

I think this issue relates to wanting the primary key to mean something. Avoid that trap. Choose an arbitrarily generated primary key that uniquely identifies a row of data and has no other meaning. Otherwise you will eventually run into the problem of wanting to alter the primary key values to preserve the meaning.

For a good explanation of why you should rely on ORDER BY to retrieve the data in the desired order instead of assuming the data will otherwise appear in a sequence determined by the primary key see Cruachan's answer to a similar question