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