[android] How to get Last record from Sqlite?

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.

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);

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))

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;"       

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

cursor.moveToLast();

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

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 |  LAST_STATUS |
+--------+--------------+
|        |              |
| 1      |  ON          |
|        |              |
| 2      |  ON          |
|        |              |
| 3      |  ON          |
+--------+--------------+

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

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#

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

SELECT * FROM table ORDER BY column DESC LIMIT 1;

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')

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;
}

To get last record from your table..

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