Is it possible to order by latest row insert desc?

Let me explain this a bit further.

I have a table in sqlite that looks something like this:

table
------
id numeric primary key,
uuid text not null,
other_field text

Now id is the standard auto-increment type. This table can have entries added and removed at any time so if a rowid that had been used once before was used again, that'd be fine. I'm not using that 'table full' feature of sqlite. There are allowed to be multiple entries with the same uuid. The idea is I'm only interested in the last inserted entry in general.

This raises the question. I know I can do a call like "select other_field from table w开发者_运维问答here uuid=? order by rowid desc"

This would be ok, but what if rowid wraps around? order by rowid desc will not give me the newest entry.

All I can think of is to add a creation_time field like

table
------
id numeric primary key,
uuid text not null,
other_field text
creation_time datetime

and then when it gets created put datetime('now') in that field.

select other_field from table where uuid=? order by creation_time desc

But this means adding an extra field and a bigger index. Is there a built in way to do this?

If you want to select the newest ID or RowID, have a look into the command LIMIT. With the LIMIT you can return only 1 row. For your example:

SELECT * from table ORDER BY id DESC LIMIT 1

With this solution, you don't have to add another field "creation_time".

Hops this helps :)