David
I have a table dumped via a Firebird export tool from an "historic" library database. The resulting table, which I import to SQLite, has no `id`, nor is there a "primary key" or the like.
This database is still updated with new material, and I would like to be able to get the last *n* rows. I assumed I could do this with a `limit` command, but that, of course, fetches the **first** *n* rows, and I cannot work out how to get the **last** *n* rows (by order of insertion).
I've done a fair bit of websearching about this, and the only things I have managed to find [combine `limit` with a `DESC` sort on the `id` field][1], or variations on that theme.
So — is it possible to do a `select * from TABLE limit...` and get the last *n* rows? (And, just for the banter, there are presently 20,269 rows in this table.)
[1]: https://stackoverflow.com/questions/12125904/ "Select last N rows from MySQL"
Top Answer
Jack Douglas
SQLite is [no](https://dba.stackexchange.com/a/5775) [different](https://dba.stackexchange.com/a/6053) [to](https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:74320098178823) other RDBMSs, without an `ORDER BY` the order of results is not guaranteed. This fact [is documented](https://www.sqlite.org/lang_select.html):
> If a SELECT statement that returns more than one row does not have an ORDER BY clause, the order in which the rows are returned is undefined.
If you want to be able to sort by insertion order, You would be well advised not to rely on some observed behaviour that is explicitly called out as undefined. This kind of thing has burned people before, like when Oracle introduced a hash `GROUP BY` in 10.2 — people who had noticed that `GROUP BY` sorted the results had been relying on that undefined behaviour and suddenly wondered why their applications broke.
A minor version bump to SQLite could cause any solution without an `ORDER BY` to stop working. An unexpected edge case in the current version could do the same.
I suggest you create the empty table with an additional column, with a default value of `current_timestamp`, before importing. The values you insert subsequently will then be (reliably) sortable on that column.
Unfortunately you can't just add a column with a non-constant default value to an existing table in SQLite:
<>https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=a9e08910903aafc20e0acc6c193e6905
Answer #2
David
So, it appears I simply didn't [look hard enough][1].
This does the job (where *n* = 10):
```
SELECT * FROM table LIMIT 10 OFFSET (SELECT count(*) FROM table) - 10;
```
But you knew that already. (If there is a better solution, or other solutions that it would help to know about, do tell!)
[1]: https://stackoverflow.com/a/4715308/ "Answer - How to SELECT the last 10 rows of an SQL table which has no ID field?"