How do I count rows based on last valid condition? (ROW_NUMBER function Invalid in SQL Server 2000)

Question

I want to count the number of rows up to the last value of ColumnB that is
greater than 0. In the sample table below, my t-sql should give a COUNT of 6 (rows).
Can you please show proper way to formulate the sql statement? 

ID	ColumnB	ColumnC	ColumnD
1	4	0	0
2	0	0	0
3	1	0	0
4	0	0	0
5	0	0	0
6	8	0	0
7	0	0	0

Answer 1

select max(ID)
from tab1
where isnull(ColumnB,0)>0;

or

select count(distinct ID)
from tab1
where isnull(ColumnB,0)>0;

---

Many Thanks & Best Regards, Hua Min

Answer 2

Hi,

Try this

with CTE
AS (
select *, case when col1<>0 then row_number()Over(order by id) else NULL end RowNUM from t2)
select MAX(rownum) from cTE

Regards
Satheesh

Answer 3

I'm not sure what you are looking for. Assuming ID column is unique here is a try:

DECLARE @MyTable TABLE
(
ID INT IDENTITY (1, 1),
ColumnB INT,
ColumnC INT DEFAULT (0),
ColumnD INT DEFAULT (0)
)

INSERT INTO @MyTable (ColumnB) VALUES
(4), (0), (1), (0), (0), (8), (0)

;WITH CTE
AS
(
SELECT *, RN = ROW_NUMBER() OVER(ORDER BY ID) FROM @MyTable
) SELECT ColumnBCount = MAX(RN) FROM CTE WHERE ColumnB > 0

- Krishnakumar S

Answer 4

We are not sure whether the ID column is in sequential order or not and is there any gaps in ID values. So the approach of MAX(id) can be wrong for some other values. Anyway only the OP can comment on this.

- Krishnakumar S

Answer 5

SELECT count(*) from yourtable where columnB>0

Answer 6

Agree with Krishnakumar, even the ID in sequence order the second solution will not provide the correct answer as it only count the time when value in ColumnB > 0, in this case it will return 3 instead of 6.

select count(distinct ID)
from tab1
where isnull(ColumnB,0)>0;

Answer 7

Hi,

you may try as below also

DECLARE @id int
SELECT @id = MAX(ID) FROM tab1 WHERE ColumnB > 0
select count(*) from tab1 where id <= @id

//Meer

Answer 8

Declare @tbl Table(id int,val int)
Insert into @tbl values (1,2),(2,0),
(3,2),(4,0),(8,5),(10,0)

select * from @tbl

select count(ID)
from @tbl
where ID < = ( select max(ID) from @tbl where val = 
											(select max(val) from @tbl)
			)											
				

and output will be

---

Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

Answer 9

I apologize for the additional question but I forgot to indicate that we are still using SQL Server 2000, and apparently ROW_NUMBER is not a valid function in this platform. How can I make (Krishnakumar S) suggested solution in SQL Server 2000 without the use of ROW_NUMBER function?

Answer 10

Is ID a column now in your table? Why not to add an identity column for such purpose?

---

Many Thanks & Best Regards, Hua Min

Answer 11

Thanks Hua Min. It is not an identity column, but the ID is in sequential order. Pls see additional (simplified) table below. This one, I have replaced the column name "ID" into "Year". I want to get the Year value of the last ColA value that is greater than 0. In the example below, my return value should be 6. Previous solution in previous replies using ROW_NUMBER was working until I applied into code that queries SQL Server 2000. Error: ROW_NUMBER is not a valid function.

Year	ColA
1	2
2	0
3	0
4	5
5	0
6	3
7	0

Answer 12

Here is the same script I have developed before in SQL Server 2008 rewritten for SQL Server 2000. You need to test the performance of the script as well before moving to production:

DECLARE @MyTable TABLE
(
ID INT IDENTITY (1, 1),
ColumnB INT,
ColumnC INT DEFAULT (0),
ColumnD INT DEFAULT (0)
)

INSERT INTO @MyTable (ColumnB) VALUES (4)
INSERT INTO @MyTable (ColumnB) VALUES (0)
INSERT INTO @MyTable (ColumnB) VALUES (1)
INSERT INTO @MyTable (ColumnB) VALUES (0)
INSERT INTO @MyTable (ColumnB) VALUES (0)
INSERT INTO @MyTable (ColumnB) VALUES (8)
INSERT INTO @MyTable (ColumnB) VALUES (0)

SELECT
	ColumnBCount = MAX(t.RN)
FROM
	(
	SELECT
		a.ID, a.ColumnB, a.ColumnC, a.ColumnD
		,
		(
		SELECT COUNT(*) + 1 FROM @MyTable b WHERE b.ID < a.ID
		) RN
	FROM
		@MyTable a
	) AS t
WHERE
	ColumnB > 0

What I've done is recreating the ROW_NUMBER feature in 2000 (an old workaround prior to 2005).

- Krishnakumar S

Answer 13

Thanks Hua Min. It is not an identity column, but the ID is in sequential order. Pls see additional (simplified) table below. This one, I have replaced the column name "ID" into "Year". I want to get the Year value of the last ColA value that is greater than 0. In the example below, my return value should be 6. Previous solution in previous replies using ROW_NUMBER was working until I applied into code that queries SQL Server 2000. Error: ROW_NUMBER is not a valid function.

Year	ColA
1	2
2	0
3	0
4	5
5	0
6	3
7	0

OK, in your case, is year incremented for every record?

---

Many Thanks & Best Regards, Hua Min

Answer 14

Hi,

You can insert data into a temp table which has identity column and then same logic could work

CREATE Table #TEMP(row_number int identity,
			ColumnB int,
			ColumnC int,
			ColumnD int)
Insert into #Temp(ColumnB,ColumnC,ColumnD) 
SELECT ColumnB,ColumnC,ColumnD FROM YOURTABLE
SELECT ColumnBCount = MAX(row_number) FROM CTE WHERE ColumnB > 0

Regards
Satheesh
-----------------------------------------
Mark as answer if the post helped

Answer 15

Satheesh's script also do the trick. However when inserting to the temporary table an ORDER BY is required, otherwise there can be inconsistancies in the result. Modify Satheesh's script as below:

Insert into #Temp(ColumnB,ColumnC,ColumnD) 
SELECT ColumnB,ColumnC,ColumnD FROM YOURTABLE ORDER BY ID

- Krishnakumar S

Answer 16

I think it's not 100% guarantee that the ID will be correct in the new table. There is a very slim chance that it will not work.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog