How to get total no. of rows , when using LIMIT in SQLite

Question

I have a table like this.

test

id      name

1       first
2       second
3       third
4       fourth
5       fifth

I am using pagination concept in my Android application .

I have to use a LIMIT of 3 rows . But also total no. of rows in this table .

How can i do this is SQLite database in one query ?

You have to write two queries, one for all of the table(and can get number of rows via cursor). — AAnkit, May 19 '15 at 12:44
@ErasmoOliveira I tried this query " SELECT id,name, COUNT(*) as total_rows FROM test LIMIT 3 " but it always returns me last row — Deepak Rathore, May 19 '15 at 12:53

score 1 · Accepted Answer · answered May 19 '15 at 13:03

1

If you want to get the total count for each retrieved row you can try the following (based on your comment):

SELECT id, name, c.total_rows
    FROM test t, (
        SELECT count(*) total_rows
            FROM test
        ) c 
    LIMIT 3

answered May 19 '15 at 13:03

antonio

. Thanks for answer it worked . But can you plz tell me how can i use this in getContentResolver().query() method ? . Thanks – Deepak Rathore May 19 '15 at 13:08
No, you can't do this kind of queries using `ContentResolver.query()`. Take a look at this: http://stackoverflow.com/questions/6338802/can-i-perform-this-android-query-with-contentresolver-query-left-join-and-ca – antonio May 19 '15 at 13:11
Fine . I will use db.rawQuery(query) . Thanks :) – Deepak Rathore May 19 '15 at 13:14
Great solution. Just wondering if this is more performant? But second thoughts if its doing the count on every single row that might be a bit overkill, no – CodeUK Jul 12 '18 at 19:06

1 Answers1