The question you're gonna get asked of course is "what are you sorting by?"
If it's for example rowid, then this convoluted thing will work, though probably inefficiently. create table tbl (bc int, temp int); insert into tbl values (35, 123), (35, 124), (35, 123), (20, 123), (12, 123), (12, 123), (16, 123), (35, 123), (35, 123), (35, 123), (35, 123), (35, 123); select count(*) from tbl where rowid > (select max(rowid) from tbl where bc != (select bc from tbl where rowid = (select max(rowid) from tbl))); If you're sorting by multiple columns then it'll get more complex. And I didn't test it with a million record table, so don't know how it'll scale. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of nitpi...@arcor.de Sent: Thursday, December 07, 2017 1:46 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] finding the number of records until a value is different Hi all, I have a DB i.e. like this: table values bc temp 35 123 35 124 35 123 20 123 12 123 12 123 16 123 35 123 35 123 35 123 35 123 35 123 The value in temp (or all the other columns) is not of interest. Now I a looking for the basic concept how to count the number of rows where bc=bc_of_last_row after the last different bc. SELECT COUNT(bc) FROM values WHERE bc=35; gives me the number of all rows with bc=35, so here 8. But I am looking for 5, the number of rows after the last change of bc. Regards Matth _______________________________________________ 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
