First of all i'm using Sqlite for 2 years now and just want to tell it's great! that's done and now here's why i'm sending this message to the list: I'm working on pagination and want to retrieve the number of row that i would normally retrieve without the Limit clause. So i hoped to use subselect to do such thing but discover a weird behaviour and perhaps a bug?
background info : - doing SELECT * FROM mytable i get 20 rows and that's fine - doing SELECT * FROM mytable GROUP BY col1 return 10 rows and that's fine too - doing SELECT count(*) as ct FROM (SELECT * FROM mytable) _TMPTABLE_; just work fine too i retrieve one row with ct = 20 BUT if i add a GROUP BY clause in the subselect the results returned in ct is just false ex: - doing SELECT count(*) FROM (SELECT * FROM mytable GROUP BY col1) _TMPTABLE_; return one row with 20 in ct and that's FALSE, this is the total amount of row in mytable instead of the amount of grouped row Am i doing something wrong? tested on a mysql database give me correct results, is this a bug, is there any way to work around? At least is there a better way to get the total amount of row a query would have return without a LIMIT clause as using SQL_CALC_FOUND_ROWS and FOUND_ROWS() in mysql (i would prefer that the subselect work in the good way) Thanks for your help nathan
