How to get Last record from Sqlite?

Question

I have a one table question_table and one ImageButton (Back). I need to get the last inserted record from the database after clicking on the Back.

My row contains the following columns: question, optionA, optionB, optionC, optionD, and I need the data for use on my Activity. I create one method in database but it's not working.

Here is code for reference:

MySQLiteHelper.java extract:

public List<ObjectiveWiseQuestion> getLastInsertQuestion()
{
    // long index = 0;
    List<ObjectiveWiseQuestion>LocwiseProfileList=new ArrayList<ObjectiveWiseQuestion>();
    db = getReadableDatabase();
    Cursor cursor = db.query(
            "sqlite_sequence",
            new String[]{"seq"},
            "name = ?",
            new String[]{TABLE_QUESTION},
            null,
            null,
            null,
            null );

    if (cursor.moveToFirst())
    {
        do {
            ObjectiveWiseQuestion owq= new ObjectiveWiseQuestion();

            owq.setQuestion(cursor.getString(2));
            owq.setOptionA(cursor.getString(3));
            owq.setOptionB(cursor.getString(4));
            owq.setOptionC(cursor.getString(5));
            owq.setOptionD(cursor.getString(6));
            owq.setCorrectOption(cursor.getString(7));
            LocwiseProfileList.add(owq);
        } while(cursor.moveToNext());

        db.close();
    }

    return LocwiseProfileList;
}

OnClickListner from AddQuestionActivity.java

imgBack.setOnClickListener( new View.OnClickListener() 
{                       
    @Override
    public void onClick(View v) 
    {
        msg();
        emptyFormField();

        try {
            final List<ObjectiveWiseQuestion> LocWiseProfile =  db.getLastInsertQuestion();       

            for (final ObjectiveWiseQuestion cn : LocWiseProfile)
            {   
                db=new MySQLiteHelper(getBaseContext());
                db.getWritableDatabase();
                txtQuestion.setText(cn.getQuestion());
                txtOptionA.setText(cn.getOptionA());
                txtOptionB.setText(cn.getOptionB());
                txtOptionC.setText(cn.getOptionC());
                txtOptionD.setText(cn.getOptionD());
                txtCorrectOption.setText(cn.getCorrectOption());
                db.close();
            }
        } catch(Exception e) {
            e.printStackTrace();
        }           
    }
});

Please give me some hint.

score 254 · Answer 1 · edited Mar 17 '18 at 11:56

254

I think the top answer is a bit verbose, just use this

SELECT * FROM table ORDER BY column DESC LIMIT 1;

edited Mar 17 '18 at 11:56

CopsOnRoad

237,138
77
654
440

answered Apr 16 '13 at 17:54

Stephen Nguyen

5,357
5
24
28

1

The "top answer" doesn't always remain the top answer. Do you mean Hasmukh's? – LarsH Jul 01 '16 at 16:17
sometimes it is giving the second-last value for some unknown reason...most of the time it is working fine – MSD Jun 24 '20 at 12:22
If the column is marked AUTOINCREMENT then SELECT MAX(ID) is better. Using the CLI execute 'EXPLAIN QUERY PLAN ' your query results in a full table scan, whereas SELECT MAX(ID) results in a much faster search. – Brian Heilig Aug 28 '20 at 12:17
If you need to grab more than one row (below uses 500 rows), and would like to re-order them try: SELECT * FROM (SELECT * FROM Table ORDER BY column DESC LIMIT 500) ORDER BY column ASC;""") ASC sorts the data from descending order to ascending order after. – Andrew Mar 20 '22 at 04:28

score 219 · Accepted Answer · edited Apr 20 '20 at 12:51

219

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;

edited Apr 20 '20 at 12:51

nandur

134
10

answered Mar 28 '12 at 06:52

Hasmukh

4,632
2
30
44

30

This is very bad request for that purpose because `sqlite` should sort all records by their id (slow) before returning the result, Stephen Nguyen gave optimal request with `DESC` and `LIMIT 1` – Artem Zinnatullin Nov 15 '13 at 08:13
1

@Hasmukh hi govind here.. want to do satelitemenu at the right of bottom corner... did you done it.. ? – GovindRathod Sep 03 '14 at 09:15
@Govind here is the reference link, it may help you, https://github.com/siyamed/android-satellite-menu/issues/3 – Hasmukh Sep 16 '14 at 07:05
@Hasmukh, if suppose that column id is not just 'AUTOINCREMENT' but something else unique... is still working this solution? – Choletski Jan 25 '16 at 08:24
@Choletski: It will only give you the last record if the column that you're ORDERing BY (or taking the MAX of) is guaranteed to increase with each new record. So you could use a timestamp column, as long as it's always increasing. – LarsH Jul 01 '16 at 16:20
2

This and other answers seem to have an unstated assumption that the column they're sorting on is always increasing. That's true of autoincrement columns, but not every table has a column like that. – LarsH Jul 01 '16 at 16:23
This is rather hacky and may lead to trouble. Process the callback and get the id using 'data.insertId' where data is whatever you name your callback return. – Théo T. Carranza Oct 18 '18 at 12:30
@ArtemZinnatullin Executing 'EXPLAIN QUERY PLAN ' the first query using MAX(ID) results in two searches, whereas the second query using ORDER BY results in a full table scan. So the query using MAX(ID) is the more optimal one. – Brian Heilig Aug 28 '20 at 12:22

score 25 · Answer 3 · answered Dec 27 '18 at 15:39

25

Here's a simple example that simply returns the last line without need to sort anything from any column:

"SELECT * FROM TableName ORDER BY rowid DESC LIMIT 1;"

answered Dec 27 '18 at 15:39

user4061742

267
3
6

1

Very nice. Makes no assumptions about the row being referenced and is probably faster sorting too. – fantabolous Sep 08 '22 at 09:41

score 25 · Answer 4 · answered Mar 28 '12 at 06:56

25

To get last record from your table..

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

answered Mar 28 '12 at 06:56

5hssba

8,079
2
33
35

1

You should add the WHERE = TABLE_NAME statement when your SQLite db has more than one table – aphoe Aug 25 '15 at 19:43
@aphoe He doesn't need WHERE = TABLE_NAME because "sqlite_sequence" is the table reference. – apkisbossin Jun 14 '16 at 19:21
I know of no guarantee that the last record in an unsorted query will be the last record of the table. I bet this works "often". You cannot rely on it though. – MotoRidingMelon Feb 15 '18 at 02:01

Leonardo Sibela · Answer 5 · 2020-04-20T00:27:53.053

12

I think it would be better if you use the method query from SQLiteDatabase class instead of the whole SQL string, which would be:

 Cursor cursor = sqLiteDatabase.query(TABLE, allColluns, null, null, null, null, ID +" DESC", "1");

The last two parameters are ORDER BY and LIMIT.

You can see more at: http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

edited Apr 20 '20 at 00:27

answered May 10 '15 at 00:06

Leonardo Sibela

1,613
1
18
39

score 10 · Answer 6 · answered Mar 28 '12 at 06:54

10

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

answered Mar 28 '12 at 06:54

waqaslam

67,549
16
165
178

is theres a way to get this through the creation of cursor on getContentResolver().query ? – ralphgabb Jul 30 '15 at 06:06
You can simply use cursor.moveToLast(); – Anant Shah Dec 01 '18 at 07:09

Saaru Lindestøkke · Answer 7 · 2023-07-03T11:58:48.787

Another option is to use SQLites LAST_VALUE() function in the following way.

Given this table:

object	status	time
1	ON	100
1	OFF	102
1	ON	103
2	ON	101
2	OFF	102
2	ON	103
3	OFF	102
3	ON	103

You can get the last status of every object with the following query

SELECT                           
    DISTINCT OBJECT,             -- Only unique rows
    LAST_VALUE(STATUS) OVER (    -- The last value of the status column
        PARTITION BY OBJECT      -- Taking into account rows with the same value in the object column
        ORDER by time asc        -- "Last" when sorting the rows of every object by the time column in ascending order
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING    -- Take all rows in the patition
    ) as lastStatus
FROM
    TABLE

The result would look like:

object	lastStatus
1	ON
2	ON
3	ON

You can select additional columns to show the last value of by simply adding it with a separate windowing function:

SELECT                           
    DISTINCT OBJECT,             -- Only unique rows
    LAST_VALUE(STATUS) OVER (    -- The last value of the status column
        PARTITION BY OBJECT      -- Taking into account rows with the same value in the object column
        ORDER by time asc        -- "Last" when sorting the rows of every object by the time column in ascending order
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING    -- Take all rows in the patition
    ) as lastStatus,
    LAST_VALUE(TIME) OVER (
        PARTITION BY OBJECT
        ORDER by time asc
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as lastTime
FROM 
    TABLE

object	lastStatus	lastTime
1	ON	103
2	ON	103
3	ON	103

Or by doing some kind of concatenation:

SELECT                           
    DISTINCT OBJECT,             -- Only unique rows
    LAST_VALUE(STATUS || ':' || TIME) OVER (    -- The last value of the status column
        PARTITION BY OBJECT      -- Taking into account rows with the same value in the object column
        ORDER by time asc        -- "Last" when sorting the rows of every object by the time column in ascending order
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING    -- Take all rows in the patition
    ) as lastStatusTime
FROM 
    TABLE

object	lastStatusTime
1	ON:103
2	ON:103
3	ON:103

What if I wanted to include the TIME column in the result while still getting only one row per OBJECT? — bernardo.g, Jul 02 '23 at 14:23
Thank you, @SaaruLindestøkke. I ended up "duplicating" the windowing function for each of the other columns I wanted. — bernardo.g, Jul 03 '23 at 14:51

score 3 · Answer 8 · answered Feb 18 '16 at 20:18

Suppose you are looking for last row of table dbstr.TABNAME, into an INTEGER column named "_ID" (for example BaseColumns._ID), but could be anyother column you want.

public int getLastId() {
    int _id = 0;
    SQLiteDatabase db = dbHelper.getReadableDatabase();
    Cursor cursor = db.query(dbstr.TABNAME, new String[] {BaseColumns._ID}, null, null, null, null, null);

    if (cursor.moveToLast()) {
        _id = cursor.getInt(0);
    }

    cursor.close();
    db.close();
    return _id;
}

score 2 · Answer 9 · edited Mar 17 '18 at 11:55

2

Just simple, you can move with Cursor moveToLast(); method provides to move to the last record

cursor.moveToLast();

edited Mar 17 '18 at 11:55

CopsOnRoad

237,138
77
654
440

answered Jan 24 '17 at 09:58

Dilavar Malek

1,157
11
18

score 2 · Answer 10 · edited Jul 10 '18 at 16:06

2

I wanted to maintain my table while pulling in one row that gives me the last value in a particular column in the table. I essentially was looking to replace the LAST() function in excel and this worked.

, (Select column_name FROM report WHERE rowid = (select last_insert_rowid() from report))

edited Jul 10 '18 at 16:06

LuFFy

8,799
10
41
59

answered Jul 10 '18 at 15:44

j casillas

21
2

score 1 · Answer 11 · answered Feb 27 '21 at 12:01

1

Also, if your table has no ID column, or sorting by id doesn't return you the last row, you can always use sqlite schema native 'rowid' field.

SELECT column 
FROM table 
WHERE rowid = (SELECT MAX(rowid) FROM table);

answered Feb 27 '21 at 12:01

androias

57
6

This is the fastest of all solutions – PerseP Nov 12 '22 at 12:27

score 1 · Answer 12 · edited Feb 15 '23 at 07:29

1

Sometimes there is no ID column and ROWID does not help.

You may use simple query, which is universal (IMHO):

Select * from TABLE_NAME limit 1 offset ((select count() from TABLE_NAME) - 1)

edited Feb 15 '23 at 07:29

SerjantArbuz

982
1
12
16

answered Feb 06 '23 at 16:02

Dmitrij

11
2

score 0 · Answer 13 · answered Jan 10 '18 at 15:30

in sqlite, there is a table called sqlite_sequence, this table contains the table name and it's last id number (if the id is auto incremented).

So, to get the last row in a table just put :

Select * from TABLENAME where id=(SELECT * from sqlite_sequence where name ='TABLENAME')

anefeletos · Answer 14 · 2020-05-22T01:56:15.507

0

The previous answers assume that there is an incrementing integer ID column, so MAX(ID) gives the last row. But sometimes the keys are of text type, not ordered in a predictable way. So in order to take the last 1 or N rows (#Nrows#) we can follow a different approach:

Select * From [#TableName#]  LIMIT #Nrows# offset cast((SELECT count(*)  FROM [#TableName#]) AS INT)- #Nrows#

edited May 22 '20 at 01:56

answered May 20 '20 at 16:50

anefeletos

672
7
19

How to get Last record from Sqlite?

14 Answers14

Linked