Have your application read the table in reverse order. Then when the value of BC changes, stop incrementing a counter and close the select. You can use whatever ordering you like just so long as it is "descending" (that is reading backwards). ((Code example in Python -- you can use wordier and/or more obfuscated languages if you so choose -- but anyone should be able to understand this code fragment)).
bc = None count = 0 for row in db.cursor().execute('select bc from db where bc is not null order by rowid desc;'): if bc is None: bc = row.bc if bc != row.bc: break count += 1 When the loop is done bc will contain the value of bc, and count will contain the count of that value since it last changed (null values completely ignored since you did not specify as part of your problem statement whether or not there are null values, and what to do with them if there are some, so I assumed that you would want to ignore them -- if this is a school assignment then it is faulty and incompletely specified and the correct answer is that no answer is possible). It will also be fast like diarrhea from goose on an excessively hot and humid summer day in Hoostun. Unless of course you will have hugely long runs of the same value at the end of the query, in which case (if say there are a million of two duplicates values at the end of the table) it might be faster to count them in SQL. Of course, YMMV and applying premature optimization to the problem is almost always counter-productive. This is because it took about 35 nanoseconds to compute the algorithm to solve the problem. Computing the correct SQL query (and testing it) will take at least a million orders of magnitude longer (meaning it will cost a million times more to implement). Time is money and all that. You can probably do it in SQL but it will be far far far far far far far slower and more inefficient. Just because you have an SQL database does not mean that *everything* must be written as a single SQL query. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of nitpi...@arcor.de >Sent: Thursday, 7 December, 2017 11:46 >To: email@example.com >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 >firstname.lastname@example.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list email@example.com http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users