Home How to Rank a Partition in T-SQL
Reply: 2

How to Rank a Partition in T-SQL

Avinash
1#
Avinash Published in 2018-02-12 21:55:44Z

I want to give each combination of CardId and InvoiceNo a Number/Rank for the below data

;WITH Test (CardId,InvoiceNo,Category,Amount) as 
(
    SELECT '1001','3001','Fees',30
    UNION ALL   SELECT '1001','3001','Non-Fees',45
    UNION ALL   SELECT '1001','3001','Service Fees',55
    UNION ALL   SELECT '1002','3002','Fees',10
    UNION ALL   SELECT '1002','3002','Non-Fees',25
    UNION ALL   SELECT '1002','3002','Service Fees',10
    UNION ALL   SELECT '1003','3010','Fees',45
    UNION ALL   SELECT '1003','3010','Non-Fees',70


)

Like this

CardId  InvoiceNo   Amount  Rank
1001    3001         30     1
1001    3001         45     1
1001    3001         55     1
1002    3002         10     2
1002    3002         25     2
1002    3002         10     2
1003    3010         45     3
1003    3010         70     3

I tried the below query with both Row_number() and Rank() but is not giving me the desired result. The rank() is ranking all the rows as 1 and the row_number() is numbering each group with 1,2,3.

SELECT CardId
,InvoiceNo
,Amount
,RANK() OVER (PARTITION BY CardID,InvoiceNo ORDER BY CardId) as RankNo

FROM Test
Joel Coehoorn
2#
Joel Coehoorn Reply to 2018-02-12 22:09:16Z

Remove the partitions. The rank function looks within the partitions where a new partition resets the rankings. Thus it was seeing every record within each partition as tied for 1st. Also, you may want dense_rank().

SELECT CardId
    ,InvoiceNo
    ,Amount
    ,DENSE_RANK() OVER (ORDER BY CardId, InvoiceNo) as RankNo    
FROM Test
ORDER BY CardId, InvoiceNo, Amount

Fiddle:

http://sqlfiddle.com/#!18/0f0c2/7

WEI_DBA
3#
WEI_DBA Reply to 2018-02-12 22:08:58Z

Try this. I think this is what you are looking for.

SELECT CardId
    ,InvoiceNo
    ,Amount
    ,DENSE_RANK() OVER (ORDER BY CardId, InvoiceNo) as RankNo

FROM Test
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.29785 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO