I would like ROW_Number() to work normally UNLESS column 'box' is Null. If 'box' is null the row number doesn't increase.
I have data that looks like this...
Row Box
1 5
2 3
3 1
4 Null
5 Null
6 2
7 8
8 Null
9 Null
I want my query to pull out data that looks like this...
Row Box
1 5
2 3
3 1
3 Null
3 Null
4 2
5 8
5 Null
5 Null
I'm trying to avoid using a cursor but I can't figure out how to get this working without one.
You can do this with a correlated subquery. Here is one way:
select (select count(box) from t t2 where t2.row <= t.row) as row,
box
from t
order by row;
This is counting the number of valid box values up to a given row.
In SQL Server 2012, you can do this with a cumulative count():
select count(box) over (order by row) as row, box
from t
order by row;
These assume that row is set as in the question. If row does not start with those values, then you have a problem. SQL tables are inherently unordered, and you need some column to specify the ordering.
