ROW_NUMBER() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

ROW_NUMBER()

Author

Topic

mivey4
Yak Posting Veteran

66 Posts

Posted - 2011-03-24 : 14:26:30

Hi!!!

I have a condition where I need to have a counter column created dynamically. I chose to use the ROW_NUMBER() Function which works very well with just one exception and that is I need to have the numbers restart from one when the grouping changes.

For example if I had 2 Customers and performed:

SELECT ROW_NUMBER() (ORDER BY stateCode, customerID, customerMonth) AS Rec, stateCode, customerID, customerMonth
FROM myData

I get:
Rec stateCode customerID customerMonth
1 2 20321 2011-03-01 00:00:00.000
2 2 20321 2011-04-01 00:00:00.000
3 2 20321 2011-03-01 00:00:00.000
4 2 20321 2011-04-01 00:00:00.000

What I want is:
Rec stateCode customerID customerMonth
1 2 20321 2011-03-01 00:00:00.000
2 2 20321 2011-04-01 00:00:00.000
1 2 20321 2011-03-01 00:00:00.000
2 2 20321 2011-04-01 00:00:00.000

Is this possible using ROW_NUMBER() or should I be exploring other options???

Plz Help!

Oracle OCA
Adaptec ACSP

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-24 : 14:31:44

[code]SELECT ROW_NUMBER() OVER (PARTITION BY customerMonth, ORDER BY stateCode, customerID, customerMonth) AS Rec,
stateCode, customerID, customerMonth
FROM myData;
[/code]

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-24 : 14:31:45

ROW_NUMBER() OVER(Partition By StateCode, CustomerId ORDER BY stateCode, customerID, customerMonth) AS Rec, stateCode, customerID, customerMonth

or whatever you want to group by

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-24 : 14:32:50

SELECT ROW_NUMBER() (PARTITON BY customerID, customerMonth ORDER BY stateCode) AS Rec, stateCode, customerID, customerMonth
FROM myData

mivey4
Yak Posting Veteran

66 Posts

Posted - 2011-03-24 : 15:33:20

Thanks ALL for the responses. The Seventhnight solution actually did the trick and I was working from bottom up on the replies. Robvolk's solution returned all 1's for the recordNumber which was the same result I got when I was trying to experiment to get it working.

I didn't try Russell's proposition but I'll check that one as well. I think the response to GROUP BY gave me a hint as to how to use the function moving forward. I guess PARTITION BY has the same meaning as GROUP BY when using the ROW_NUMBER() Function. That really helped a lot.

Thanks ALL!!!

Oracle OCA
Adaptec ACSP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources