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.

196706 次浏览
小开
最佳答案

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;
小开

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
小开

To get last record from your table.. 

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

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

SELECT * FROM table ORDER BY column DESC LIMIT 1;
小开

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

小开

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

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

cursor.moveToLast();
小开

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

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

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#
小开

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          |
+--------+--------------+
小开

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

提交答案