SQL Server counting number of rows from beginning of sorted table where colvalue = 0
We have SQL Server 2008 R2. I have table
sort by column
Nr (ORDER BY MyTable.NR)
count how many values are equal to 0 from the beginning of this sorted table (taking in mind that it is already sorted by NR) (
MyTable.Value = 0).
For instance, if
MyTable has these values:
NR Value 2 0 1 0 3 5 4 0
then I have to get count = 2, because if sort this table by Nr we have two rows where Value = 0 from the beginning of the table.
2 Solutions collect form web for “SQL Server counting number of rows from beginning of sorted table where colvalue = 0”
You can do this using
NOT EXISTS to exclude all rows where a row exists with a lower
NR and a
Value <> 0. e.g.
DECLARE @T TABLE (NR INT, Value INT); INSERT @T VALUES (2, 0), (1, 0), (3, 5), (4, 0); SELECT COUNT(*) FROM @T AS t1 WHERE t1.Value = 0 AND NOT EXISTS ( SELECT 1 FROM @T AS t2 WHERE t2.Value <> 0 AND t2.NR < t1.NR );
You can filter data using predicate
Value = 0, then number filtered data with
row_number, and then count rows, where assigned number equal to original one:
declare @data table (NR int, Value int); insert into @data values (2, 0), (1, 0), (3, 5), (4, 0); ;with step1 as (select NR from @data where Value = 0), step2 as (select NR, rn = row_number() over (order by NR) from step1) select count(1) from step2 where NR = rn;