How to Get Last Record from Sqlite

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;

How to read the last record in SQLite table?

There is a function named sqlite3_last_insert_rowid() which will return the integer key for the most recent insert operation. http://www.sqlite.org/c3ref/last_insert_rowid.html

This only helps if you know the last insert happened on the table you care about.

If you need the last row on a table, regardless of wehter the last insert was on this table or not, you will have to use a SQL query

SELECT * FROM mytable WHERE ROWID IN ( SELECT max( ROWID ) FROM mytable );

SQLite: How to get last record?

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

How to get last records with conditions from SQLite?

SQLite has a rowid that you can query over it like this:

select *   
from Table1 t
where exists (
  select 1
  from Table1 ti  
  where t.course = ti.course
  group by ti.course
  having t.rowid = max(ti.rowid);    -- filtering rows that are newest rows in table
  );

SQLite Fiddle Demo

Sample Data:

| id |    course |      ... hidden rowid |
|----+-----------|      ... -------------|
|  1 |      math |      ...            1 |
|  2 |   english |      ...            2 |
|  3 |      math |      ...            3 |
|  4 |   english |      ...            4 |
|  5 | chemistry |      ...            5 |
|  8 |      test |      ...            6 |
|  7 |      test |      ...            7 |

Result:

| id |    course |      ... hidden rowid |
|----+-----------|      ... -------------|
|  3 |      math |      ...            3 |
|  4 |   english |      ...            4 |
|  5 | chemistry |      ...            5 |
|  7 |      test |      ...            7 |

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.

Fetching last row from sqlite database

If you have already got the cursor, then this is how you may get the last record from cursor:

cursor.moveToPosition(cursor.getCount() - 1);

then use cursor to read values

or

do it like this

Cursor cursor = db.rawQuery(selectQuery, null);
cursor.moveToLast();

or

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

Android / Java: How to get last row value in sqlite?

you are closing cursor before getting it's value

try this :

String selectQuery= "SELECT * FROM " + TABLE_XYZ+" ORDER BY column DESC LIMIT 1";
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
String str = "";
if(cursor.moveToFirst())
    str  =  cursor.getString( cursor.getColumnIndex(KEY_TEXT) );
cursor.close();
return str;

get first and last element from sqlite database

SQLite does support UNION but does not like the queries inside the parentheses.

Use each of your queries as a subquery:

select * from (select * from location order by id ASC LIMIT 1)
UNION
select * from (select * from location order by id DESC LIMIT 1);

---

Related Topics