Maybe use a temp table as another possible solution? create temp table queryResults (id int); insert into queryResults select rowid from Tbl where Col > ?1 order by Col; select count(*) from queryResults; select id from queryResults order by rowid; drop table queryResults;
The whole issue of CTE's being calculated more than once is an issue, otherwise I would say... with queryResults as (select rowid from Tbl where Col > ?1 order by Col) select count(*) from queryResults union all select rowid from queryResults; Then the first record would be the count, followed by all the rowids. But it looks like it's running the CTE twice, which defeats the advantage. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of x Sent: Friday, January 19, 2018 8:50 AM To: SQLite mailing list Subject: Re: [sqlite] [EXTERNAL] get stmt row count Keith & Simon, are you not both missing the point? I want the rows and the count but without having to run two queries. I tried the following (let stmt1 represent the original query and stmt2 the count(*) version of that query). stmt1 took 6+ secs for the first step. stmt2 took 6+ secs to get the count. Counting using int Count=1; while (sqlite3_step(stmt1)) Count++; after the first step took under 2 secs BUT in order to then get the rows I’d have to reset stmt1 which would result in the pre-first step code being run again at the first step call (another 6+ secs down the drain). I’m thinking (but I’m by no means sure) that sqlite’s pre-first step code should be able to calculate the row count as it went along at virtually no cost. I realise it would only be a solution for queries sorted by non-indexed columns (i.e. where sqlite’s pre-first step code had to consider all result set rows). _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
