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
CREATE TABLE x
(
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.
Marc
-----Original Message-----
From: sqlite-users [mailto:[email protected]] On
Behalf Of [email protected]
Sent: Thursday, December 7, 2017 1:46 PM
To: [email protected]
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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
