Skip to content
Advertisement

Is using LIMIT in SQLite gives the data or rows in “FIRST IN FIRST OUT” basis?

I am using SQLite in my Ionic project, I am saving my Log Files in SQLite server, for this I want to limit the .db file with only last 2000 data values in FIRST IN FIRST OUT basis, does using

LIMIT 2000

will give me the last 2000 values stored or the first 2000 values I have stored (which is not my requirement).

Thanks for the help in advance .

Advertisement

Answer

SQL queries return unordered sets unless the query has an ORDER BY. This is actually an extension of the fact that SQL tables represent unordered sets. There is simply no ordering.

So, using LIMIT with no ORDER BY returns arbitrary rows. It may look like the rows are ordered, but they are not — at least no by SQL standards. You need an ORDER BY.

One caveat: I will admit that SQLite is such a simple database that it only allow one thread per query. As such, it might actually “reliably” return results in a particular order. However, I have never found documentation that guarantees this behavior and contradicts the SQL standard. So, relying on the ordering of a result set with no ORDER BY is quite risky, even if it seems to work.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement