Ranking rows with non linear order

Question

I have to rank these rows:

Type	Name	Score
Dog	Teddy	50
Dog	Max	10
Dog	Rocky	70
Cat	Zoe	45
Dog	Buddy	20
Dog	Daisy	30
Dog	Duke	20

In this particular way:

Type	Name	Score	Rank
Dog	Rocky	70	1
Dog	Teddy	50	2
Cat	Zoe	45	3
Dog	Daisy	30	3
Dog	Buddy	20	4
Dog	Duke	20	4
Dog	Max	10	6

Without the 'Cat' type, it's a normal RANK() SQL Function.

The 'Cat' in the ranking should not stole a position of any other dogs. 'Cat' type must have the same rank of the next 'Dog' type regardless of the score.

The RANK() functionality is required for all 'dog' type (in the example Buddy and Duke are 4th and Max 6th)

A possibility is create a temporary table/stored procedure and post-elaborate results... but I would like to know if there is an easier way to to this.

Answer 1

Adjust the Cat score to be the following Dog score, then rank. (Or dense rank... your desired results don't match either as it stands).

WITH cte AS (
    SELECT
        Type,
        Name,
        Score,
        MAX(CASE WHEN Type = 'Dog' THEN Score END) OVER (ORDER BY Score ASC) NewScore
    FROM YourTable
)
SELECT Type, Name, Score,
  DENSE_RANK() OVER (ORDER BY NewScore DESC) DenseRank,
  RANK() OVER (ORDER BY NewScore DESC) Rank
FROM cte
ORDER BY Score DESC;

Returns:

Type	Name	Score	DenseRank	Rank
Dog	Rocky	70	1	1
Dog	Teddy	50	2	2
Cat	Ted	46	3	3
Dog	Daisy	30	3	3
Dog	Buddy	20	4	6
Dog	Duke	20	4	6
Dog	Max	10	5	8

Oh I may have misunderstood... from your desired results, maybe you want to rank the Dogs first, then the Cats just join in... in that case this is what you need

WITH cte AS (
    SELECT
        Type,
        Name,
        Score,
        CASE WHEN Type = 'Dog' THEN RANK() OVER (PARTITION BY CASE WHEN Type = 'Dog' THEN 1 END ORDER BY Score DESC) END DogRank
    FROM YourTable
)
SELECT Type, Name, Score,
    MIN(DogRank) OVER (ORDER BY Score ASC) Rank
FROM cte
ORDER BY Score DESC;

Returns:

Type	Name	Score	Rank
Dog	Rocky	70	1
Dog	Teddy	50	2
Cat	Ted	46	3
Dog	Daisy	30	3
Dog	Duke	20	4
Dog	Buddy	20	4
Dog	Max	10	6

DBFiddle

Answer 2

A possible solution is a combination of:

• RANK() with the appropriate partition (to rank only the rows where the Type is Dog) and the correct ORDER BY clause.
• LEAD() with IGNORE NULLS (introduced in SQL Server 2022).

Note, that if you want to get a rank with no gaps in the ranking values, you may use DENSE_RANK() instead of RANK().

Sample test data:

SELECT *
INTO Data
FROM (VALUES  
   ('Dog', 'Teddy', 50),
   ('Cat', 'Mary', 1),
   ('Cat', 'Ana', 70),
   ('Dog', 'Max', 10),
   ('Dog', 'Rocky', 70),
   ('Cat', 'Zoe', 45),
   ('Cat', 'Nelly', 40),
   ('Dog', 'Buddy', 20),
   ('Dog', 'Daisy', 30),
   ('Dog', 'Duke', 20)
) v (Type, Name, Score)  

T-SQL:

SELECT
   Type, Name, Score, DogRank,
   Rank = COALESCE(
      DogRank, 
      LEAD(DogRank) IGNORE NULLS OVER (ORDER BY Score DESC, DogRank),
      COUNT(*) OVER ()
      -- or MAX(DogRank) OVER () + 1 
   )
FROM (  
   SELECT 
      *,
      DogRank = CASE WHEN Type = 'Dog' THEN RANK() OVER (PARTITION BY CASE WHEN Type = 'Dog' THEN 1 END ORDER BY Score DESC) END
   FROM Data
) t  
ORDER BY Score DESC, DogRank

Result:

Type	Name	Score	DogRank	Rank
Cat	Ana	70	null	1
Dog	Rocky	70	1	1
Dog	Teddy	50	2	2
Cat	Zoe	45	null	3
Cat	Nelly	40	null	3
Dog	Daisy	30	3	3
Dog	Duke	20	4	4
Dog	Buddy	20	4	4
Dog	Max	10	6	6
Cat	Mary	1	null	10