Home How to Rank a Partition in T-SQL

# How to Rank a Partition in T-SQL

user7796
1#
user7796 Published in September 21, 2018, 8:05 am

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

• Try removing PARTITION BY CardID,InvoiceNo and see what you come up with. I removed it and I think the results could be what you are looking for. The rankings are 1,4 and 7 respectively for the CardID... – WEI_DBA Feb 12 at 22:06
• Correction... change it to DENSE_RANK() OVER (ORDER BY CardId, InvoiceNo) as RankNo and you'll get what you want. – WEI_DBA Feb 12 at 22:07