SQLite Forum

How to implement a table working like a ring buffer
Except that this is dependent on presumptions about ordering and availability of indexes and the preference for their use, so rather than "work exactly as intended, just very slowly" if the assumptions are not met you will get some other inscrutable result instead which may or may not implement your intention.

In the end if you use the more explicit spelling you will get precisely and exactly the same code generated when the required presumptions of the LESS EXPLICIT spelling and the less explicit spelling is used.

Also, with the more explicit spelling, you will not have the problem of someone (perhaps even you yourself) trying to remember the "meaning" tomorrow or next week or next year or a decade hence.

Plus, of course, if someone changes the definition of the indexes or adds additional indexes the explicit spelling will explicitly do exactly what you spelt, whereas the less explicit spelling is dependent for its correct operation on conditions which may or may not remain invariant.

This specifically refers to the use of an ORDER BY clause.  Eliding the LIMIT 1 or what is selected -- and using a constant -- should not make any difference.

For example, the "LIMIT 1" is "transparently added" by SQLite3.  This is, however, reliance on an "implementation detail".  Even though it is unlikely to change, it is not portable and someone familiar with a different implementation may be "confused" as to why it works and an error is not thrown.