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:
 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 

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: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] finding the number of records until a value is
>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
>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
>Regards Matth
>sqlite-users mailing list

sqlite-users mailing list

Reply via email to