SQL Server counting number of rows from beginning of sorted table where colvalue = 0

We have SQL Server 2008 R2. I have table MyTable.

I need:

  • How to bind html to an rdlc file
  • Is there a way to set a default cursor type in PDO (specifically PDO_SQLSRV)?
  • Stored procedure EXEC vs sp_executesql difference?
  • Does LINQ2SQL automatically put ExecuteCommand in a transaction
  • Improving performance for large table ordered top query
    1. sort by column Nr (ORDER BY MyTable.NR)

    2. 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.

  • Bulk insert with text qualifier in SQL Server
  • Can the script for median be generalised and put into a function type structure
  • SQL Query - Have to determine if it is a specific day of the month (ex: 2 Tuesday of month)
  • Override alphabetical default ORDER BY with a UNION of 2+ tables?
  • How can I drop all indexes in a SQL database with one command?
  • Convert Columns of Data to Rows of data in SQL Server
  • 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.

    INSERT @T VALUES (2, 0), (1, 0), (3, 5), (4, 0);
    FROM    @T AS t1
    WHERE   t1.Value = 0
            (   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);
    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;
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.