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

The result I'm looking for is

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.

CodePudding user response：

This is a type of gaps-and-islands problem. You are trying to get a ranking number for each group of identical CardNumber values (with no gaps), when ordered by ID DESC.

You cannot use DENSE_RANK or ROW_NUMBER for this, because they will place all rows with the same CardNumber value together.

There are a number of solutions. Here is one:

WITH PrevValues AS (
     SELECT *,
       IsNewCardNumber = CASE WHEN CardNumber = LAG(CardNumber) OVER (ORDER BY ID DESC)
                        THEN NULL ELSE 1 END
     FROM XXXX
)
SELECT
  ID,
  Rank = COUNT(IsNewCardNumber) OVER (ORDER BY ID DESC),
  CardNumber,
  StampNumber,
  AuditDate
FROM PrevValues;

db<>fiddle