Android SQLite Query - Getting latest 10 records

android sql sqlite android-sqlite
Gaurav Arora picture Gaurav Arora · Dec 24, 2012 · Viewed 39.4k times · Source

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

Michael Dillon picture Michael Dillon · Dec 24, 2012

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.

Related questions

SQLite in Android How to update a specific row

I've been trying to update a specific row for a while now, and it seems that there are two ways to do this. From what I've read and tried, you can just use the: execSQL(String sql) method or the: …

Read More
Best way to work with dates in Android SQLite

I'm having some trouble working with dates on my Android application that uses SQLite. I have a couple questions: What type should I use to store dates in SQLite (text, integer, ...)? Given the best way to store dates how do …

Read More
Android SQLite: Update Statement

I need to implement SQLite in my application. I followed this tutorial.. Creating and using databases in Android one Everything is working fine. I inserted 1 row with 5 columns. Now I want to update value of 1 column only and others will …

Read More