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

Answer 1

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.

Answer 2

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