returning a Row_number and resetting the counter based on the max value of another field

Based on the data below I am trying to create a new column called theRow which will count the number of rows for each value in col2 up to the number in col3.  I have included the values that I am trying to return in theRow column below. Notice in col2 there are 5 rows with the value D.  The row needs to reset after the max of col3

col1     col2      col3  theRow

1           A          2        1
2           A          2        2
3           B          1        1
4           C          3        1
5           C          3        2
6           C          3        3
7           A          1        1
8           A          1        1
9           D          2        1
10          D          2        2
11          D          2        1
12          D          2        2
13          D          2        1
rquirionAsked:
Who is Participating?
 
tim_csCommented:
Somebody might be able to come up with an easier solution or improve my math but this is working for me.  
DECLARE @temp table(col1 int, col2 varchar(5),col3 int)

INSERT INTO @temp
VALUES   (1,'A',2)
		,(2,'A',2)
		,(3,'B',1)
		,(4,'C',3)
		,(5,'C',3)
		,(6,'C',3)
		,(7,'C',3)
		,(8,'A',1)
		,(9,'A',1)
		,(10,'D',2)
		,(11,'D',2)
		,(12,'D',2)
		,(13,'D',2)
		,(14,'D',2)
		,(15,'D',2)
		,(16,'D',2)
		,(17,'D',2)
		,(18,'D',2)
		

SELECT
	b.col1
	,b.col2
	,b.col3
	,CASE WHEN b.RN > b.col3 THEN 
		CASE WHEN b.RN%b.col3 = 0 THEN 
			b.RN - (((b.RN/b.col3)-1)*b.col3) 
		ELSE 
			b.RN - (b.RN/b.col3*b.col3)
		END 
	ELSE	
		b.RN
	END
FROM
	(
	SELECT
		a.col1
		,a.col2
		,a.col3
		,ROW_NUMBER() OVER (PARTITION BY a.col2, a.col3 ORDER BY a.col1) RN
	FROM
		@temp a
	) b
ORDER BY
	col1

Open in new window

0
 
santhimurthydCommented:
You can work like this

SELECT col1     , col2      , ROW_NUMBER() OVER(PARTITION BY col3 ORDER BY theRow ASC) AS 'Row Number'
FROM Table
0
 
Scott PletcherSenior DBACommented:
OVER(PARTITION will partition the entire table.

The values look to need to reset every time col2 changes **from the previous col2, in order by col1**.

Grrrr and ouch!

0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
santhimurthydCommented:
Run this Querry in Adventorworks of 2008 Database, will give you create info as the Row_Number getting started on MiddleName change

SELECT FirstName, LastName, ROW_NUMBER() OVER(PARTITION BY MIddleName ORDER BY MIddleName DESC) AS 'Row Number', MIddleName , SalesYTD, PostalCode
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;
0
 
Scott PletcherSenior DBACommented:
For testing purposes, I removed row#6 and added row#14.

1           A          2        1
2           A          2        2
3           B          1        1
4           C          3        1
5           C          3        2
7           A          1        1
8           A          1        1
9           D          2        1
10          D          2        2
11          D          2        1
12          D          2        2
13          D          2        1
14          C          3        1 --<<-- should be 1, right OP?

If you partition by col2, but ignoring col1, you would assign 3 to that value.  

The sample data MAY just happen to be distributed evenly.  I ASSUME it doesn't have to be because of rows 7-8 and 9-13 (??).  I could EASILY be wrong, though.

IF the input data will ALWAYS have a sequence frequency that is an even multiple of col3, then partitioning and ignoring col1 should work.  Otherwise, probably not.
0
 
rquirionAuthor Commented:
Thank you very much.  This worked bang on!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.