Solved

ROW_NUMBER() over partition question

Posted on 2010-11-18
8
408 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
  • 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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:ewangoya
ID: 34170372

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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS - Date Report Options 2 29
Help With SQL Query 9 33
Current Month Filter in Visual Studio 10 23
MS SQL + group by time 4 15
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

830 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