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?

It's not a good idea to name a column `rowid`. That already has a special meaning in SQLite. — dan04, Dec 31 '10 at 21:17

score 16 · Answer 1 · edited Oct 09 '18 at 10:23

16

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;

edited Oct 09 '18 at 10:23

answered Jun 19 '13 at 08:57

Sasq

486
3
7

Can anyone comment whether there is a single legitimate use case for this? For instance, in 2018 I worked on a postgres project where I did end up coming to the conclusion that for a read-only database it was faster to have tables pre-ordered since results would immediately materialize so I could stream them and not wait until the full set of rows was ordered. Is the same true for SQLite? This is the only post that instructs how to do this for SQLite, and there are tons of posts on the contrary that advise never to do this (always `order` _during_ `select`) – ecoe Jun 27 '22 at 15:07
Despite the upvotes, this answer is very misleading. Tables in SQL, including SQLite, are conceptually unordered collections of rows and without an `ORDER BY` are returned in arbitrary, unspecified order that [can even differ on two queries of the same table](https://stackoverflow.com/q/56096228/9952196). The database might be able to avoid sorting based on its internal knowledge of the table storage, but that doesn't mean you can get away with not using `ORDER BY` when you want a specific order. – Shawn Jun 27 '22 at 17:52

score 4 · Answer 2 · edited May 23 '17 at 10:30

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

Sort an entire SQLite table

2 Answers2

Linked