SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table YourTable (Type varchar(12), Name varchar(12), Score int);

insert into YourTable (Type, Name, Score)
values
('Dog','Teddy',50),
('Dog','Max',10),
('Dog','Rocky',70),
('Cat','Zoe',45),
('Cat','Ted',46),
('Dog','Buddy',20),
('Dog','Daisy',30),
('Dog','Duke',20);
8 rows affected

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; 


Type
Name
Score
Rank


Dog
Rocky
70
1

Dog
Teddy
50
2

Cat
Ted
46
3

Cat
Zoe
45
3

Dog
Daisy
30
3

Dog
Duke
20
4

Dog
Buddy
20
4

Dog
Max
10
6


Warning: Null value is eliminated by an aggregate or other SET operation.


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; 


Type
Name
Score
DenseRank
Rank


Dog
Rocky
70
1
1

Dog
Teddy
50
2
2

Cat
Ted
46
3
3

Cat
Zoe
45
3
3

Dog
Daisy
30
3
3

Dog
Buddy
20
4
6

Dog
Duke
20
4
6

Dog
Max
10
5
8


Warning: Null value is eliminated by an aggregate or other SET operation.

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