Android SQLite Query - Getting latest 10 records

Question

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

you will need to change your query using ORDER BY clause as `SELECT * FROM tblmessage ORDER BY _id DESC LIMIT 10` — ρяσѕρєя K, Dec 24 '12 at 07:31
@Durpan If it is an SQLite database then it always has a `rowid` field (unless you specifically create the table with the `WITHOUT ROWID` clause). The `rowid` column is always indexed so will give fast results too. — Arthur Tacca, Feb 03 '20 at 09:10

score 41 · Accepted Answer · edited Nov 16 '20 at 00:55

41

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.

edited Nov 16 '20 at 00:55

Thomas Gak-Deluen

2,759
2
28
38

answered Dec 24 '12 at 08:17

Michael Dillon

31,973
6
70
106

I want to reverse the order they come in. How Can I > – Gaurav Arora Dec 24 '12 at 08:53
1

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 How Can I get the same – Gaurav Arora Dec 24 '12 at 09:01
1

Awesome! I wasn't aware that you could have nested selects like this. – Matt Logan Mar 05 '14 at 00:26
1

For the proper ordering see @moodboom's answer – NuSkooler Jun 30 '16 at 04:31
Should be SELECT ASC FROM DESC. Not DESC FROM ASC. Ascending gives you 1,2,3,4,5,6... Descending gives you 30,29,28,27... So first select descending as in the ones with highest value and down, form that select everything and flip the sort order. – user3342816 Oct 09 '19 at 01:48
A key point to note is that this query will only work quickly if you create an index on the field that you order by. This is obvious of you're used to SQL databases, but maybe not obvious to all readers here. If you just want database internal order, you can specify the `rowid` field in the `ORDER BY` clause (which is always indexed so you don't need to create an index yourself). – Arthur Tacca Feb 03 '20 at 09:20

McPeppr · Answer 2 · 2015-03-04T18:26:15.377

28

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

edited Mar 04 '15 at 18:26

answered Mar 04 '15 at 17:04

McPeppr

687
8
10

There is chance in this that offset could go negative which could raise exception – Atul Jun 25 '16 at 07:12
Of course you might call values off the upper and lower boundaries. However sqlite doesn't throw an exception, but returns all values possible. – McPeppr Jun 25 '16 at 08:31
I used this option, and it works as I want. It still safe with negative values. – Erdi İzgi Jul 27 '16 at 21:46
**This does not work universally!** Without `ORDER BY` the results will be returned in arbitrary order. If you have a `WHERE` clause with an index, SQLite will return a different subset of data depending on its internal index lookup. – ge0rg Dec 08 '17 at 15:41
This solution MUCH faster and works even if the table has less rows than the maximum limit your are trying to get. It is great because if there are less rows than you just get all the rows in the table – RaduS Mar 24 '19 at 09:14
This answer is much worse than the others: when using an `OFFSET`, SQLite has to go through ALL of the rows in between, which will be very slow for a large table. This is also true for `COUNT`, so this query is double slow! Using the nested `ASC` / `DESC` queries from the top answer will be much faster so long as (a) you specify an `ORDER BY` column, which you have to do anyway otherwise you rows might come back in a random order, and (b) the column in the `ORDER BY` is indexed, which it always will be if you order by `rowid`. – Arthur Tacca Feb 03 '20 at 09:16

score 9 · Answer 3 · edited May 23 '17 at 12:26

9

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

edited May 23 '17 at 12:26

Community

1
1

answered Jul 19 '15 at 14:21

moodboom

6,225
2
41
45

yes - select * ... (select * ... DESC limit 10) ... ASC – Tigran Sarkisian Dec 07 '16 at 10:49

No_Rulz · Answer 4 · 2013-11-18T08:52:03.643

1

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.

edited Nov 18 '13 at 08:52

answered Dec 24 '12 at 07:39

No_Rulz

2,679
1
20
33

score 1 · Answer 5 · answered Sep 15 '15 at 16:04

1

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

answered Sep 15 '15 at 16:04

voronnenok

712
6
6

This would give last 10 records but in REVERSE order. – Atul Jun 25 '16 at 07:17

score 1 · Answer 6 · answered Oct 16 '20 at 10:19

1

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

answered Oct 16 '20 at 10:19

Premanand R G

11
1
2

score 0 · Answer 7 · answered Dec 24 '12 at 08:24

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.

score 0 · Answer 8 · edited Aug 12 '17 at 09:31

0

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

You cannot have better solutions than this.

edited Aug 12 '17 at 09:31

Pang

9,564
146
81
122

answered Aug 12 '17 at 09:03

rs11

65
9

Mąñîşh Şhârmá Mkş · Answer 9 · 2017-09-03T18:34:30.990

0

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

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

edited Sep 03 '17 at 18:34

answered Sep 03 '17 at 17:40

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

86
1
4

Some comment about why this is the right answer might help the person asking the question – joanolo Sep 03 '17 at 18:00
I also face same type of problem in a Chatting App .but i am done by do this cursor.moveToLast(); while (cursor.moveToPrevious()){ //do something } – Mąñîşh Şhârmá Mkş Sep 06 '17 at 04:37

Android SQLite Query - Getting latest 10 records

9 Answers9

Linked