Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

ROW_NUMBER() over partition question

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
elucero
Asked:
elucero
  • 4
  • 3
1 Solution
 
appariCommented:
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
 
eluceroAuthor Commented:
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
 
appariCommented:
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
eluceroAuthor Commented:
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
 
appariCommented:
if thats what you want try this

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

0
 
eluceroAuthor Commented:
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
 
appariCommented:
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
 
Ephraim WangoyaCommented:

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

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now