Does SQLite guarantee the order of results to match a table?

Question

If table Scores looks like this:

_id | score
---------
  1 | 1,000
  2 | 2,000
  3 | 3,000
  4 | 4,000
  5 |    -1
  6 |    -1
  7 |    -1

Will the following query always return the rows in _id ascending order?

SELECT * FROM Scores

Also, will the following query always return the first ordered occurrence of _id (that is, 5)?

SELECT _id FROM Scores WHERE Score = -1 LIMIT 0, 1

The key here is ALWAYS. I have tested it and it works as intended but I want to verify this outcome is guaranteed and that an order by clause is not needed in these cases.

Thank you all.

I suggest you to use order by clause, for smiley solution for this issue. — Pratik Dasa, Jun 16 '14 at 07:03

score 1 · Answer 1 · answered Jun 16 '14 at 06:57

1

One of the principles of the databases is that you can not suppose that your registers are ordered in any way, as the internal implementation of SQLite may differ between devices. You should use the operator order by to assure a certain order.

answered Jun 16 '14 at 06:57

zozelfelfo

3,776
2
21
35

score 1 · Accepted Answer · answered Jun 16 '14 at 07:01

1

By default, the rows are logically stored in order of increasing rowid.

And if your select stament does not include an order by, or the table has no index, the default sorting order will always be the primary key column, which is the _ID column in this case

Read more here

answered Jun 16 '14 at 07:01

NeedAnswers

1,411
3
19
43

The linked article nowhere mentions the order in which rows are returned. – Mitch Wheat Jun 16 '14 at 07:02
i believe it's in section 2.1 – NeedAnswers Jun 16 '14 at 07:08
1

Nope. That says "SELECT * FROM fruitsforsale ORDER BY rowid;" Notice the order by? The fact that the optimiser removes the need for the sort is a slightly different matter... – Mitch Wheat Jun 16 '14 at 07:11
Yes, found it. It does seem to be guaranteed. Thanks a lot. – Jun 16 '14 at 07:11
The "or" must be "and"; a covering index can change the order even without ORDER BY. – CL. Jun 16 '14 at 08:12

Does SQLite guarantee the order of results to match a table?

2 Answers2