Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLite Insertion Order vs Query Order?

Tags:

android

sqlite

Will the order of rows returned by a query will be the same as the order in which the rows were inserted into the table, of SQLite database?

If Yes, Is this behaviour consistent?

If No, Can this be enforced?

I have a requirement of storing approx 500 rows of data, and which requires sorting/ordering from time to time. The data is in proper order, before the insertion.

like image 824
ap400200 Avatar asked Apr 15 '11 08:04

ap400200


People also ask

Does column order matter in SQLite?

Will the order of the columns in the insert statement have any impact on the insert speed? (Maybe based on how the SQLite code is written it will be faster to have the columns in the same order as they are defined in the table definition?) No. This can make no possible difference.

How does SQLite order work?

SQLite sorts rows by AlbumId column in ascending order first. Then, it sorts the sorted result set by the Milliseconds column in descending order. If you look at the tracks of the album with AlbumId 1, you find that the order of tracks changes between the two statements.

How do I sort a table in SQLite?

Use the ORDER BY keyword and the name of the column by which you want to sort. This way, you'll sort the data in ascending order by this column. You could also use the ASC keyword to make it clear that the order is ascending (the earliest date is shown first, the latest date is shown last, etc.).


2 Answers

Given the small number of rows in your table, this is probably what you need:

SELECT * FROM yourtable ORDER BY ROWID

For more information on ROWID, see these two links: SQLite Autoincrement and ROWIDs and the INTEGER PRIMARY KEY

like image 195
Kal Avatar answered Oct 07 '22 13:10

Kal


Even if the order may be consistent in one scenario, there is afaik no guarantee.

That is why SQL has the ORDER BY operator:

SELECT foo,bar FROM Table FOO WHERE frobnitz LIKE 'foo%' ORDER BY baz ASC;
like image 32
Heiko Rupp Avatar answered Oct 07 '22 14:10

Heiko Rupp