CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
DeCaf
DeCaf

Reputation: 6096

Efficient ROW_NUMBER increment when column matches value

I'm trying to find an efficient way to derive the column Expected below from only Id and State. What I want is for the number Expected to increase each time State is 0 (ordered by Id).

+----+-------+----------+
| Id | State | Expected |
+----+-------+----------+
|  1 |     0 |        1 |
|  2 |     1 |        1 |
|  3 |     0 |        2 |
|  4 |     1 |        2 |
|  5 |     4 |        2 |
|  6 |     2 |        2 |
|  7 |     3 |        2 |
|  8 |     0 |        3 |
|  9 |     5 |        3 |
| 10 |     3 |        3 |
| 11 |     1 |        3 |
+----+-------+----------+

I have managed to accomplish this with the following SQL, but the execution time is very poor when the data set is large:

WITH Groups AS 
(
   SELECT Id, ROW_NUMBER() OVER (ORDER BY Id) AS GroupId FROM tblState WHERE State=0
)
SELECT S.Id, S.[State], S.Expected, G.GroupId FROM tblState S
   OUTER APPLY (SELECT TOP 1 GroupId FROM Groups WHERE Groups.Id <= S.Id ORDER BY Id DESC) G

Is there a simpler and more efficient way to produce this result? (In SQL Server 2012 or later)

Upvotes: 1

Views: 81

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Other method uses subquery :

select *,
       (select count(*)
        from table t1
        where t1.id < t.id and state = 0
       ) as expected
from table t;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269853

Just use a cumulative sum:

select s.*,
       sum(case when state = 0 then 1 else 0 end) over (order by id) as expected
from tblState s;

Upvotes: 5

Related Questions