Ok.... I'm on this list because I love reading all the comments and discussion 
about sqlite and DBs in general.  I haven't used sqlite in quite awhile, so I 
don't know how well this will work, but...

Assuming you have a row number as well, such as

        row_number int,
        bc int

Then you can use something like:

SELECT a.last_rn - (SELECT MAX(row_number) FROM x WHERE bc != a.last_bc)
FROM (SELECT TOP 1 bc AS last_bc, row_number AS last_rn FROM x ORDER BY 
row_number DESC) a

I know this works on MS SQL SERVER.  There may be a more efficient way, and 
you'll need to modify it based on size of table and available indexes.  I think 
the above should work fairly well if you have indexes on both the row_number 
and bc.


-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of nitpi...@arcor.de
Sent: Thursday, December 7, 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

Confidentiality notice: This e-mail is intended solely for use of the 
individual or entity to which it is addressed and may contain information that 
is proprietary, privileged, company confidential and/or exempt from disclosure 
under applicable law. If the reader is not the intended recipient or agent 
responsible for delivering the message to the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this 
communication is strictly prohibited. If you have received this communication 
in error, please notify the sender by reply e-mail or collect telephone call 
and delete or destroy all copies of this e-mail message, any physical copies 
made of this e-mail message and/or any file attachment(s).
sqlite-users mailing list

Reply via email to