Solved

ROW_NUMBER() over partition question

Posted on 2010-11-18
8
420 Views
Last Modified: 2012-05-10
how do I use the row_number() over partition when I need the partition to be with two columns?

I tried this below, but it didn't work right.  It skipped rownumbers.  Where it should of been row 1 was 3 instead.  

ROW_NUMBER() over(partition by(customer_nbr+ acct_nbr ) order by customer_nbr, acct_nbr asc) as rn

the data is this, the rn is what I need it to be
customer_nbr     acct_nbr    acct type     acct rep  rn
3343                           0             corp           mq          1
3343                            1             corp          sk            2
3343                            2              outs          dk            3
0
Comment
Question by:elucero
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 39

Expert Comment

by:appari
ID: 34169825
what output are you getting and what are you expecting?
with the sql and the data you posted you will get

customer_nbr     acct_nbr    acct type     acct rep  rn
3343                           0             corp           mq         1
3343                            1             corp          sk           1
3343                            2              outs          dk          1
0
 

Author Comment

by:elucero
ID: 34169959
the result I want is below,  I want to partition by both the customer_nbr and acct_nbr, thats why I did the customer_nbr + acct_nbr but its not working correctly


ROW_NUMBER() over(partition by(customer_nbr+ acct_nbr ) order by customer_nbr, acct_nbr asc) as rn

result I want:
customer_nbr     acct_nbr    acct type     acct rep  rn
3343                           0             corp           mq          1
3343                            1             corp          sk            2
3343                            2              outs          dk            3

0
 
LVL 39

Expert Comment

by:appari
ID: 34170026
if you want the output as in your post you have to partition by only Customer_nbr. once you include acct_nbr in partition you will get the output posted in my previous post.
for the following data, what output do you want?

customer_nbr     acct_nbr    acct type     acct rep  
3343                           0             corp           mq      
3343                            1             corp          sk          
3343                            2              outs          dk  
3342                          0            XXXX           XX
3342                          1            YYYY              YY
3344                          0             ZZZZ            XX
3344                          1            RRRR               SS
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:elucero
ID: 34170119
see rn below

customer_nbr     acct_nbr    acct type     acct rep  rn
3343                           0             corp           mq            1
3343                            1             corp          sk              2
3343                            2              outs          dk             3
3342                          0            XXXX           XX               1
3342                          1            YYYY              YY             2
3344                          0             ZZZZ            XX             1
3344                          1            RRRR               SS        2
0
 
LVL 39

Accepted Solution

by:
appari earned 250 total points
ID: 34170248
if thats what you want try this

ROW_NUMBER() over(partition by(customer_nbr ) order by customer_nbr, acct_nbr asc) as rn

0
 

Author Comment

by:elucero
ID: 34170265
I did do this and it works most of the time but sometimes I get nulls for rn 1 and the data ends up in rn 3
0
 
LVL 39

Expert Comment

by:appari
ID: 34170291
can you post the sample how you are getting nulls for rn 1 and data in rn 3?
is this is complete sql or you are posting part of the sql? if possible post actual SQL.
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34170372

select *, ROW_NUMBER() over (partition by customer_nbr order by customer_nbr, acct_nbr asc) rn
from yourtable
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question