CODEWITHSUNDEEP
androidsqlite
ap400200
ap400200

Reputation: 340

SQLite Insertion Order vs Query Order?

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.

Upvotes: 15

Views: 6558

Answers (3)

Kal
Kal

Reputation: 1717

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

Upvotes: 16

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95751

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?

No, you can't count on that. All query optimizers have a lot of freedom when it comes to speeding up queries. One thing they're free to do is to return rows in whatever order is the fastest. That's true even if a particular dbms supports clustered indexes. (A clustered index imposes a physical ordering on the rows.)

There's only one way to guarantee the order of returned rows in a SQL database: use an ORDER BY clause.

Upvotes: 4

Heiko Rupp
Heiko Rupp

Reputation: 30994

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;

Upvotes: 6

Related Questions