CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008
AHS
AHS

Reputation: 87

SQL Server row_number() Partition by query

I have my data in below format and trying to get unique customerIDCount using and accountCategoryount using row_number() and partition by but having issue with below query , can anyone pls help ?

with cte as 
(
    select 
        *,
        accountCategoryCount = row_number() over (partition by phoneNUmber, ID, accountCategory Order by phoneNumber) 
    from 
        (select distinct * from myTable) base
),
cet2 as 
(
    select 
        *,
        customerIDCount = row_number() over (partition by phoneNumber, ID order by phoneNumber 
    from 
        cte A
)
select * from cte2

MyTable data:

phoneNumber ID   name  dob      accountCategory accountNumber balance
123456789   456  sam   10061970 checking        4567          10
123456789   456  sam   10061970 checking        4568          200
123456789   456  sam   10061970 Savings         4569          12
123456789   456  sam   10061970 Savings         4570          13
123456789   789  John  10101970 CreditCard      4571          400
123456789   789  John  10101970 CreditCard      4572          600
123456789   789  John  10101970 Savings         4573          50

Expected Result

phoneNumber ID   name  dob      accountCategory accountNumber balance accountCategoryCount customerIDCount 
123456789   456  sam   10061970 checking        4567          10      1                    1
123456789   456  sam   10061970 checking        4568          200     2                    1
123456789   456  sam   10061970 Savings         4569          12      1                    1
123456789   456  sam   10061970 Savings         4570          13      2                    1
123456789   789  John  10101970 CreditCard      4571          400     1                    2
123456789   789  John  10101970 CreditCard      4572          600     2                    2
123456789   789  John  10101970 Savings         4573          50      1                    2

Upvotes: 2

Views: 10350

Answers (1)

Alberto Martinez
Alberto Martinez

Reputation: 2670

The problem is that ROW_NUMBER() always return different numbers and you want to obtain the same number for the same values of "phoneNumber" and "ID", for this you need to use DENSE_RANK() which returns the same value for ties:

with cte as (
    select *,
        row_number() over (partition by phoneNumber, ID, accountCategory Order by phoneNumber) as accountCategoryCount,
        dense_rank() over (order by phoneNumber, ID) as customerIDCount
    from 
        (select distinct * from myTable) base
)
select * from cte

Results:

| phoneNumber |  ID | accountCategory | ... | accountCategoryCount | customerIDCount |
|-------------|-----|-----------------|-----|----------------------|-----------------|
|   123456789 | 456 |        checking |     |                    1 |               1 |
|   123456789 | 456 |        checking |     |                    2 |               1 |
|   123456789 | 456 |         Savings |     |                    1 |               1 |
|   123456789 | 456 |         Savings |     |                    2 |               1 |
|   123456789 | 789 |      CreditCard |     |                    1 |               2 |
|   123456789 | 789 |      CreditCard |     |                    2 |               2 |
|   123456789 | 789 |         Savings |     |                    1 |               2 |

Upvotes: 5

Related Questions