CODEWITHSUNDEEP
sqlite
ffppp
ffppp

Reputation: 23

SQLite: How to get last record?

I have a SQLite database and want to get the last record.

I know of ORDER BY column DESC LIMIT 1. However, the reason that's not good enough is that this database A) has no indices and I don't want to create any, and no primary key either, B) is quite large, think of order 1bn rows.

CREATE TABLE my_table (
    col1 TEXT,
    col2 float,
   )

Now, the rows are on the physical disk in the same order that they were inserted in, so getting the last one should be trivial. This order in MySQL would, I believe, correspond to the primary key which in MySQL always must be present, so I could use the ORDER BY primary_key DESC LIMIT 1 solution. But here I don't know what to do because as far as I can tell there is no primary key that corresponds to the disk ordering.

I did a SELECT COUNT(*) FROM my_table; (which takes 15 minutes to run) and so I know the exact number of rows in the table, lets call it N. If I had an AUTOINCREMENT PRIMARY KEY I could do WHERE primary_key > N-1, but again this seems to be missing.

Upvotes: 2

Views: 2078

Answers (2)

MikeT
MikeT

Reputation: 56943

You always have an index unless you specify WITHOUT ROWID, that is there is a hidden column called rowid (AUTOINCREMENT which can only be coded when INTEGER PRIMARY KEY is specified) (INTEGER PRIMARY KEY defines the column as an alias of rowid) (AUTOINCREMENT imposes a constraint that the value, when generated by SQLite, is higher than any existing).

You could try to use ORDER by rowid BUT wuthout AUTOINCREMENT there isn't a guarantee that the highest rowid will be the last row (but until you hit 9223372036854775807 rows it should be (assuming you don't mess with the rowid)).

With AUTOINCREMENT you have additional overheads especially when inserting as it not only has to retrieve the highest rowid (which it typically adds 1 to) but it also has to retrieve the respective row from the sqlite_sequence table. SQLite Autoincrement

Upvotes: 0

PurTahan
PurTahan

Reputation: 983

using this code:

select * from my_table order by rowid desc LIMIT 1

rowid is built-in field in sqlite tables Read More : https://www.sqlite.org/lang_createtable.html#rowid

Upvotes: 3

Related Questions