CODEWITHSUNDEEP
androidsqlite
user3265248
user3265248

Reputation:

How to get particular amount of records from sqlite android

In my app there are many records (let's suppose 100 records) available in sqlite. What I want is to get last 50 records from database. And I am using below code for this purpose.

String [] projection = new String[]{
            ID, MESSAGE_ID, MESSAGE_TYPE, MESSAGE_MEDIA_STATUS, MESSAGE_BODY, MESSAGE_MEDIA_REFERENCE,
            MESSAGE_MEDIA_TYPE, MESSAGE_MEDIA_SIZE, MESSAGE_SENDER_ID, MESSAGE_RECIPIENT_ID,
            MESSAGE_DIRECTION, MESSAGE_STATUS, MESSAGE_LOCAL_STATUS, MESSAGE_TIMESTAMP};
    String selection = MESSAGE_SENDER_ID + "=?" + " AND " + MESSAGE_RECIPIENT_ID + "=?" + " OR " + MESSAGE_SENDER_ID + "=?" + " AND " + MESSAGE_RECIPIENT_ID + "=?";
    String [] selectionArgs = new String[]{String.valueOf(senderId), String.valueOf(recipientId), String.valueOf(recipientId), String.valueOf(senderId)};
    String orderBy = ID + " DESC";
    String limit = "50";
    Cursor cursor = null;
    try {
        cursor = sqLiteDatabase.query(TABLE_MESSAGES, projection, selection, selectionArgs, null, null, orderBy, limit);

        if (cursor.moveToFirst()) {
    //cursor reading stuff
    }

Mentioned code is working as expected as I want. It gets last 50 records but the problem is, it gets last 50 records in desc order. Lets suppose I have record from 1 to 100 numbers in sequence. My code first changes its order (because query has desc clause)that means record will be in sequence of 100 to 1 and "limit 50" clause gets first 50 record. And now here is the problem now my record will be 100,99,98,,,and last record would be 51 but i want my records in asc order as it was originally in asc in DB(Record should be fetched as 51,52 and so on till 100). I have some solution for this as well. I know I can do sorting stuff from java comparator.My question is there any better way to solve this problem?Or any sqlite query that can solve the same problem rather fetching data and after populating sorting stuff.

Upvotes: 0

Views: 62

Answers (2)

Shruti
Shruti

Reputation: 815

select * from (select [COLUMN_NAME] from [TABLE_NAME] order by ID DESC limit 50) order by ID ASC;

Inner select query will select the last 50 records so the sort order is DESC. And outer select query will select all the records queried by inner select and sort it in ascending order so ASC is needed.

Upvotes: 1

CL.
CL.

Reputation: 180060

You have to use one ordering for the LIMIT, and another ordering for the result. So you need a subquery:

SELECT ...
FROM (SELECT ...
      ...
      ORDER BY ID DESC
      LIMIT 50)
ORDER BY ID;

To be able to execute this, you have to use rawQuery() instead.

Upvotes: 2

Related Questions