3

I´m using Sqlite3 on a grid application pretty much like that post here..

The grid needs the rows that is being show and the total number of rows found, used for paging.

On Oracle I use the following statement to get rows from 100 to 500 - fields Id, Name, Phone where Deleted=false:

SELECT * FROM (SELECT ROW_NUMBER() 
              OVER (ORDER BY ID)  AS RN, 
              COUNT(*) OVER (ORDER BY (SELECT NULL) AS CNT) 
              Id, Name, Phone FROM MyTable WHERE Deleted='F') 
T WHERE RN > 100 AND RN < 500;

On MySQl, I normally use the excellet SELECT SQL_CALC_FOUND_ROWS followed by a SELECT FOUND_ROWS() call.

So my questions are:

a) Is there any equivalent of this Sqlite3 for either Oracle or MySQL option above ? b) How can I accomplish that on Sqlite3 without issuing 2 selects (one for querying and another one for counting) ?

The question posted here does not solve my problem because it does not return the number of records, just pages through the table.

Thanks a lot for helping...

Community
  • 1
  • 1
Mendes
  • 17,489
  • 35
  • 150
  • 263
  • possible duplicate of [Efficient paging in SQLite with millions of records](http://stackoverflow.com/questions/14468586/efficient-paging-in-sqlite-with-millions-of-records) – John Bollinger Apr 28 '15 at 16:24
  • That does not solve my problem because it does not return the total number of records found... That´s why I´m using the `OVER` and the `SQL_CALC_FOUND_ROWS` mechanism. – Mendes Apr 28 '15 at 18:47
  • 1
    SQLite doesn't support window queries. You can't really get the count without a separate query, or by counting the rows yourself. – Colonel Thirty Two Apr 28 '15 at 18:53

1 Answers1

1

In recent versions of SQLite (3.25.0 and later) there is support for window functions. So you can rewrite your query as follows:

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID) AS RN, 
               COUNT() OVER () AS CNT,
               Id, Name, Phone FROM MyTable WHERE Deleted='F') 
T WHERE RN > 100 AND RN < 500;
Altair7852
  • 1,226
  • 1
  • 14
  • 23
  • SQLite 3.25 OS support: Android 11 (API level 30) (SQLite 3.28) ; iOS 13.0 (SQLite 3.28.0) ; Sadly this means you can't use this on e.g. Android 7 :/ – jave.web Mar 15 '22 at 11:55
  • What worked for me on Android 7 was a basic subquery `someId, someOtherColumn, (SELECT COUNT(someId) FROM yourTableNameGoesHere) AS c` @Altair7852 do you know any advantage of using `OVER()` window over this? – jave.web Mar 15 '22 at 11:58