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