Get Last Inserted Value from SQLite Database Android

Get last inserted value from sqlite database Android

Well actually the SQLiteDatabase class has its own insert method which returns the id of the newly created row. I think this is the best way to get the new ID.
You can check its documentation here.

I hope this helps.

How to get last inserted row in SQlite android

Try this code

String selectQuery = "SELECT  * FROM " + "sqlite_sequence";
Cursor cursor = db.rawQuery(selectQuery, null);
cursor.moveToLast();

or

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

or

SELECT * FROM table ORDER BY column DESC LIMIT 1;

Android Sqlite get last insert row id

Your SQL statrment will return all the row ids, not just the latest. Try something like this...

SELECT ROWID from SQL_LITE_SEQUENCE order by ROWID DESC limit 1

Also note that I believe selecting from SQL_LITE_SEQUENCE will get the latest ID from ANY table, you can also access the SQL_LITE_SEQUENCE by selecting ROWID on any table, and getting just the IDs for that table. IE

SELECT ROWID from MYTABLE order by ROWID DESC limit 1

And thanks to MisterSquonk for pointing out the next step in the comments, adding it here for ease of reference later...

The query statement will then return a Cursor object containing the results, so to access the integer value you would do something like this (I'll substitute more common methods for your helper method, just for others sake)

String query = "SELECT ROWID from MYTABLE order by ROWID DESC limit 1";
Cursor c = db.rawQuery(query);
if (c != null && c.moveToFirst()) {
    lastId = c.getLong(0); //The 0 is the column index, we only have 1 column, so the index is 0
}

(Note that although the SQL Lite docs call ROWID and Integer, it is a 64 bit integer, so in Java it should be retrieved as a long.)

How to get last inserted row guid in SQLite?

SQLiteDatabase's insert() returns an integer primary key even if you defined another type of primary key in your table. Simply use the result from insert() to fetch your guid:

SELECT guid FROM Foo WHERE rowid = x;

How to get Last record from Sqlite?

Try this:

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

OR

you can also used following solution:

SELECT * FROM tablename ORDER BY column DESC LIMIT 1;

Retrieve Last Record in SQLite Table (again)

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.

How to get last inserted row id from sqlite in phonegap android

You can get the id of the last insert on a table like this -

tx.executeSql("INSERT INTO profile('name','label','list_order','category') values(?,?,?,?)", 
    [currentProfile.name, currentProfile.label, currentProfile.list_order, currentProfile.category], 
    function(tx, results){
        var lastInsertId = results.insertId; // this is the id of the insert just performed
    }, 
    failCB
)

The results.insertId in WebSQL is similar to mysql_insert_id() in MySQL -

mysql_query("INSERT INTO mytable (product) values ('kossu')");
printf("Last inserted record has id %d\n", mysql_insert_id());

how can I get the id of the last inserted event

how can I get the id of the last inserted event?

The int-based primary/auto-inc value is obtained via the object that you inserted (the object instance is updated during the Insert with the new value).

var newRecord = new Record { SomeString = "StackOverflow" };
var numofRecordsInserted = conn.Insert(newRecord);
Console.WriteLine($"Newly inserted id = {newRecord.Key}");

is this even a good way do to deal with the situation

That depends upon your application. A pri-key/auto-inc field does prevent the reuse of the generated IDs, that is until the database is reset (via deletion/re-creation or a reset via the the sqlite_sequence table).

Should you use it as a "foreign" key, lots of opinions here, some yes, some no.... Personally if you never use it externally to your app (i.e. it is not transmitted/sync'd via a remote API) it works well/fast as a foreign key if you understand how SQLite creates it and when it can/could be reset, see the linked docs.

If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

re: https://sqlite.org/autoinc.html

---

Related Topics