Re copy and re-order table

Question

I am using SQLite. I want to create a new table ordered differently than an existing tables's data. I have tried the following but it does not work in either the Mozzilla SQLite admin tools or SQLite Manager. What am I doing wrong?

INSERT INTO temp  (SnippetID, LibraryID,Name,  BeforeSelection, AfterSelection, ReplaceSelection, NewDocument, NewDocumentLang, Sort)
                           SELECT (SnippetID, LibraryID,Name,  BeforeSelection, AfterSelection, ReplaceSelection, NewDocument, NewDocumentLang, Sort)
                           FROM Snippets  ORDER BY LibraryID;

Thanks - JZ

Answer 1

My question to you is a simple "Why?". SQL is a relational algebra and data sets only have order when you specify it on extraction.

It makes no sense to talk about the order of your data since your database is free to impose whatever order it likes. Any decent database will not care one bit the order in which records are inserted, only the keys and constraints and other properties that can be used to efficiently store and retrieve the data.

Only when you extract data with a select ... order by does the order have to be fixed. If you want to be able to efficiently extract data ordered by your LibraryID column, simply index it and use an order by clause when extracting the data.

Answer 2

By default the table is "ordered" on the primary key, or the first column in the table, which is often the primary key anyways. when you do

select * from mytable

it uses this default "order". Everything Pax says is true though and I +1 to it.