Solved

ROW_NUMBER() over partition question

Posted on 2010-11-18
8
383 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
 

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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now