sorting - 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;