Ранжирование строк с нелинейным порядком

Отрегулируйте оценку кошки так, чтобы она соответствовала следующей оценке собаки, а затем ранжируйте ее. (Или плотный ранг... желаемые результаты в нынешнем виде тоже не совпадают).

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;

Возврат:

О, я, возможно, неправильно понял... исходя из желаемых результатов, возможно, вы хотите сначала ранжировать собак, а затем просто присоединиться к кошкам... в этом случае это то, что вам нужно.

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;

Возврат:

DBFiddle

Возможным решением является сочетание:

• RANK() с соответствующим разделом (чтобы ранжировать только те строки, где Type находится Dog) и правильным предложением ORDER BY.
• LEAD() с IGNORE NULLS (введено в SQL Server 2022).

Обратите внимание: если вы хотите получить рейтинг без пробелов в значениях рейтинга, вы можете использовать DENSE_RANK() вместо RANK().

Пример данных испытаний:

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)  

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

Результат: