Retrieve Last Record in SQLite Table (again)

Question

I did a couple searches first, and still have a question.

Far and away, the best answer I've found is in this SO answer. Summarized:

SELECT * FROM TABLE WHERE ID = (SELECT MAX(ID) FROM TABLE);

What I want to know is if the following method work just as well (because I'm too lazy to change it and I happen to like it).

SELECT * FROM TABLE ORDER BY ID DESC LIMIT 1

According to the SQLite Autoincrement documentation we have,

If no ROWID is specified on the insert, or if the specified ROWID has a value of NULL, then an appropriate ROWID is created automatically. The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert. If the table is initially empty, then a ROWID of 1 is used. [emphasis added]

and further,

The normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDs as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID. If you ever delete rows or if you ever create a row with the maximum possible ROWID, then ROWIDs from previously deleted rows might be reused when creating new rows and newly created ROWIDs might not be in strictly ascending order. [emphasis added]

My question about my proposed SQL statement comes from the last sentence. It seems to indicate that a previously deleted ROWID can be reused if you delete any row, regardless of the whether the maximum ROWID has been used. If we assume that the maximum ROWID is never used (either manually or by just that many records), then we only need to worry about deleting records. In such a case, the first SQLite documnetation excerpt makes it sound like you won't run into any problems with my query.

Does anyone know for sure?

I like my query because it involves only one select (albiet, at the cost of an order by). I suppose, I'm probably focusing too much on tiny optimizations, aren't I?

EDIT: Actually, re-reviewing the SQLite page referenced above, I think that using the AUTOINCREMENT keyword guarantees a monotonically increasing ROWID. The last section talks at length about it.

If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly different ROWID selection algorithm is used. The ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before existed in that same table. If the table has never before contained any data, then a ROWID of 1 is used. If the table has previously held a row with the largest possible ROWID, then new INSERTs are not allowed and any attempt to insert a new row will fail with an SQLITE_FULL error.

Seems pretty clear to me now. Just can't fill up the table (maximum of 9223372036854775807).

Sorry that I had only skimmed this portion earlier!

Solution 1

The two queries you show are equivalent.

As to whether they return the last record, that depends on what you mean by "last." If you delete rows you cannot rely on either query to return the last inserted row.

Solution 2

Figured I'd put a little note here about the speed that the two queries run.
On a table with about 15,000 rows, grabbing 1400 of them and then getting the last entered one, here's what results I got:

SELECT macAddr, time, onPeakTotalIn, onPeakTotalOut, offPeakTotalIn, offPeakTotalOut FROM trafficMon WHERE macAddr="02:d2:ee:aa:aa:aa" ORDER BY time DESC LIMIT 1;

CPU Time: user 0.008124 sys 0.000000
Virtual Machine Steps: 58741
Fullscan Steps: 14156

SELECT macAddr, time, onPeakTotalIn, onPeakTotalOut, offPeakTotalIn, offPeakTotalOut FROM trafficMon WHERE id = (SELECT MAX(id) FROM trafficMon WHERE macAddr="02:d2:ee:aa:aa:aa");

CPU Time: user 0.000295 sys 0.000000
Virtual Machine Steps: 49
Fullscan Steps: 3

As you can see, it's a lot easier to do the MAX(id) resource wise and takes much less time to do it.

While 0.008124s is bugger all time, when you run the same command on a MIPS based embedded device (in my case, a router), running it 20-30x in a script along with a bunch of other stuff, it all adds up.

Hope that helped :)

Ducky

Solution 3

If table has a field the type 'autoincrement', then is better to use:

SELECT seq FROM sqlite_sequence WHERE name='TABLE_NAME'

see the sample (for table 'customers'):

SELECT seq FROM sqlite_sequence WHERE name='customers'

Solution 4

First of all you should retrieve last inserted row id then from that row id you can get last inserted record by your select query.

RowID = sqlite3_last_insert_rowid(sqlite3Database)

SELECT * FROM tableName where RowID=RowID

this select statement will give you a last inserted record

get more detail in this link.

View more solutions

Comments

[CatShoes]

Please see my edit to the question. I think with a properly declared field they can ensure that the last row inserted is returned. Even in the presence of deleted rows, unless I am misunderstanding that last quote.

[Doug Currie]

Yes, if you use AUTOINCREMENT you will get the last inserted row.

[CatShoes]

Thanks! Just wanted to be sure before I implemented.

[MZaragoza]

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes.

[Satyendra Chauhan]

yes you are right @MZaragoza now I have tried to clear my answer thanks for your suggestion.