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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.