Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ROW_NUMBER() over partition question

Posted on 2010-11-18
8
Medium Priority
?
432 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 1000 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

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!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

661 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