CODEWITHSUNDEEP
sqlsqliteselectlimit
Anna K.
Anna K.

Reputation: 1995

Query that doesn't order results

SELECT stuff FROM table LIMIT 0, 50

I'm repeating this query, while increasing the offset with 50 until I get no more results.

Will the order of the results be the same every time? I don't want to use ORDER BY because I don't need them ordered so it may decrease performance unnecessarily.

I just want to make sure that whatever the order is, it stays the same between queries so I don't get duplicate records in the other queries. From the tests I made it seems I don't...

Upvotes: 0

Views: 205

Answers (2)

amrith
amrith

Reputation: 973

You are correct, if you don't specify an ORDER BY, the order is not guaranteed. For what you want to do, you SHOULD specify an explicit ORDER BY.

EDITED to reflect comments

Anna, you are correct, if you perform this sequence of queries

SELECT stuff from table LIMIT 0, 50
SELECT stuff from table LIMIT 51, 50
SELECT stuff from table LIMIT 101, 50
... and so on ...

and you don't get a row in more than one result set, that is lucky.

If you repeat the query

SELECT stuff from table LIMIT 51, 50

a million times, there is a good chance that you'll get the same results each time but there's no guarantee.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269963

If your table as a primary key, then do:

SELECT stuff
FROM table
ORDER BY pk
LIMIT 0, 50;

Ordering by a primary key should be efficient.

In SQLite, you can also do:

SELECT stuff
FROM table
ORDER BY rowid
LIMIT 0, 50;

rowid is a special "column" available for queries that is associated with the physical location of the row. Ordering by rowid should also be efficient.

Upvotes: 1

Related Questions