'Trying to add new column with DENSE_RANK() in sequentially

I'm hoping some examples will help explain the situation.

SELECT 
    ID,
    --ROW_NUMBER() OVER (PARTITION BY CardNumber ORDER BY ID DESC) AS 'RN',
    DENSE_RANK() OVER (ORDER BY CardNumber DESC) AS Rank,
    CardNumber,
    StampNumber,
    AuditDate,
FROM [dbo].[XXXX]
ORDER BY ID DESC, AuditDate DESC, StampNumber DESC

I've read up on DENSE_RANK() and it's the closest to what I'm looking for but not quite there. Running this block of code gives me

ID Rank CardNumber StampNumber AuditDate
46 1 3 20 2022-03-07 03:45:50.343
45 1 3 20 2022-03-07 03:45:50.343
44 2 2 40 2022-03-07 03:45:50.343
43 2 2 30 2022-03-07 03:45:50.343
42 2 2 20 2022-03-07 03:45:50.343
41 2 2 10 2022-03-07 03:45:50.343
40 3 1 40 2022-03-07 03:45:50.343
39 3 1 30 2022-03-07 03:45:50.343
38 3 1 20 2022-03-07 03:45:50.343
37 3 1 10 2022-03-07 03:45:50.343
36 1 3 40 2022-03-07 03:45:50.343
35 1 3 30 2022-03-07 03:45:50.343
34 1 3 20 2022-03-07 03:45:50.343
33 1 3 10 2022-03-07 03:45:50.343

The result I'm looking for is

ID Rank CardNumber StampNumber AuditDate
46 1 3 20 2022-03-07 03:45:50.343
45 1 3 20 2022-03-07 03:45:50.343
44 2 2 40 2022-03-07 03:45:50.343
43 2 2 30 2022-03-07 03:45:50.343
42 2 2 20 2022-03-07 03:45:50.343
41 2 2 10 2022-03-07 03:45:50.343
40 3 1 40 2022-03-07 03:45:50.343
39 3 1 30 2022-03-07 03:45:50.343
38 3 1 20 2022-03-07 03:45:50.343
37 3 1 10 2022-03-07 03:45:50.343
36 4 3 40 2022-03-07 03:45:50.343
35 4 3 30 2022-03-07 03:45:50.343
34 4 3 20 2022-03-07 03:45:50.343
33 4 3 10 2022-03-07 03:45:50.343

I'd like the dense rank to still group the rank by the CardNumber but need the rank column to grow sequentially instead of resetting. I'm looking to only grab the top 3 ranks.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source