'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 |
|---|
