CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
MAK
MAK

Reputation: 7270

SQL Server 2008 R2: DENSE_RANK() with two columns

I have the following data:

Table: 

CREATE TABLE tbl1
(
    cola varchar(20),
    colb int
);  

INSERT INTO tbl1 VALUES('AB10029',1),('5023154',17),('05021286',17),('10029',32),('05023154',17),('5021286',17);

Given records:

Cola        Colb    
------------------
AB10029     1   
5023154     17  
05021286    17  
10029       32  
05023154    17  
5021286     17

Note: I want to given the rank is 1 for non matching combination of cola (last characters should be same), colb and matching combination should be 1,2,3 and so on..

Expected output:

Cola        Colb    Rn
-----------------------
AB10029     1       1
10029       32      1
5023154     17      1
05023154    17      2
5021286     17      1
05021286    17      2

My try:

SELECT Cola,Colb,
   DENSE_RANK() OVER(PARTITION BY Colb ORDER BY RIGHT(Cola,5)) rn
FROm tbl1
ORDER BY RIGHT(Cola,5)

Upvotes: 1

Views: 2236

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

As a note: You can do this with dense_rank():

SELECT Cola, Colb,
   DENSE_RANK() OVER (PARTITION BY Colb, RIGHT(Cola, 5) ORDER BY Cola DESC) rn
FROm tbl1
ORDER BY RIGHT(Cola, 5);

I think ROW_NUMBER() is more intuitive for the data you have presented. However, you may want duplicates to have the same value, in which case dense_rank() is the right approach.

Upvotes: 2

Aswani Madhavan
Aswani Madhavan

Reputation: 816

You should use column name for partition in Partition By

SELECT Cola,Colb,RIGHT(Cola,5),
       ROW_NUMBER() OVER(PARTITION BY RIGHT(Cola,5),Colb ORDER BY Colb) rn
FROM tbl1
ORDER BY RIGHT(Cola,5)

Upvotes: 0

Serkan Arslan
Serkan Arslan

Reputation: 13403

You can use it

SELECT Cola,Colb,
   ROW_NUMBER() OVER(PARTITION BY Colb,RIGHT(Cola,5) ORDER BY RIGHT(Cola,5)) rn
FROm tbl1
order by RIGHT(Cola,5),rn

Result:

Cola                 Colb        rn
-------------------- ----------- --------------------
AB10029              1           1
10029                32          1
05021286             17          1
5021286              17          2
05023154             17          1
5023154              17          2

Upvotes: 5

Related Questions