CODEWITHSUNDEEP
androidsqlsqliteandroid-sqlite
Gaurav Arora
Gaurav Arora

Reputation: 8362

Android SQLite Query - Getting latest 10 records

I have a database saved in my Android application and want to retrieve the last 10 messages inserted into the DB.

When I use:

Select * from tblmessage DESC limit 10;

it gives me the 10 messages but from the TOP. But I want the LAST 10 messages. Is it possible?

Suppose the whole table data is -

1,2,3,4,5....30

I wrote query select * from tblmessage where timestamp desc limit 10

It shows 30,29,28...21

But I want the sequence as - 21,22,23...30

Upvotes: 26

Views: 42775

Answers (9)

Michael Dillon
Michael Dillon

Reputation: 32392

Change the DESC to ASC and you will get the records that you want, but if you need them ordered, then you will need to reverse the order that they come in. You can either do that in your own code or simply extend your query like so:

select * from (
    select *
    from tblmessage
    order by sortfield ASC
    limit 10
) order by sortfield DESC;

You really should always specify an order by clause, not just ASC or DESC.

Upvotes: 41

Premanand R G
Premanand R G

Reputation: 11

 "SELECT *  FROM( SELECT *  FROM " + tablename + whereClause + " ORDER BY timestamp DESC LIMIT 10)  ORDER BY timestamp ASC";

Upvotes: 1

Mąñîşh Şhârmá Mkş
Mąñîşh Şhârmá Mkş

Reputation: 86

cursor.moveToLast();
while (cursor.moveToPrevious()){
       //do something
}

with same query: select * from tblmessage where timestamp desc limit 10

Upvotes: 0

rs11
rs11

Reputation: 65

select * from
(select * from table_name order by yourfield ASC limit 10)
order by yourfield DESC;

You cannot have better solutions than this.

Upvotes: 0

voronnenok
voronnenok

Reputation: 722

If your table contains a column with primary key autoincrement (some "row_id" for example) then you just need single select with DESC order by this column

Raw request looks like 

select * from table_name order by row_id DESC limit 10

Android implementation is

private Cursor queryLastEvents() {
    return getDatabase().query("table_name", null, null, null, null, null, "row_id DESC", "10");
}

Upvotes: 1

moodboom
moodboom

Reputation: 6912

Slightly improved answer:

select * from (select * from tblmessage order by sortfield DESC limit 10) order by sortfield ASC;

Michael Dillon had the right idea in his answer, but the example gives the first few rows, inverted order: 

select * ... (select * ... ASC limit 10) ... DESC

He wanted the last, it should be: 

select * ... (select * ... DESC limit 10) ... ASC

Upvotes: 9

McPeppr
McPeppr

Reputation: 707

on large databases, the ORDER BY DESC statement really might slow down the system, e.g. raspberry pi. A nice approach to avoid ORDER BY is the OFFSET command. And you even keep the stored order:

SELECT * FROM mytable LIMIT 10 OFFSET (SELECT COUNT(*) FROM mytable)-10;

see: http://www.sqlite.org/lang_select.html

check out your performance with:

.timer ON

Upvotes: 28

No_Rulz
No_Rulz

Reputation: 2719

Try this,

SQLiteDatabase database = getReadableDatabase();
Cursor c=database.rawQuery("sql Query", null);
if(c.moveToFirst) {
    int curSize=c.getCount()  // return no of rows
    if(curSize>10) {
       int lastTenValue=curSize -10;
       for(int i=0;i<lastTenValue;i++){
          c.moveToNext();
       }
    } else {
       c.moveToFirst();
    }
}

Then retrieve the last 10 data.

Upvotes: 1

CL.
CL.

Reputation: 180172

In your query, the DESC is interpreted as a table alias.

As mentioned by ρяσѕρєя K, to be able to specify a sorting direction, you need to sort in the first place with an ORDER BY clause.

The column to be sorted should be a timestamp, if you have one, or an autoincrementing column like the table's primary key.

Upvotes: 0

Related Questions