Порядковый номер и максимальное значение с использованием DENSE_RANK

Question

Я использую DENSE_RANK назначить последовательный номер различным значениям [y] в каждой группе [x], Добавляет новый столбец [rank],

Я также хочу новый столбец, показывающий максимальный рейтинг [y] (самый высокий порядковый номер) в каждом [x] например [highest_rank], Есть ли способ получить этот новый столбец одновременно, без необходимости создавать отдельную таблицу для максимальных значений, а затем объединять таблицы вместе?

SELECT [x],[y],[rank]
INTO table2
FROM
(
SELECT *, DENSE_RANK() OVER (PARTITION BY [x] ORDER BY [y] ) AS [rank]
FROM table1
) tmp

Answer 1

Если я вас правильно понял, вы ищете что-то вроде этого:

;WITH cte1 AS (
    SELECT *,DENSE_RANK() OVER (PARTITION BY [x] ORDER BY [y] ) AS [rank]
    FROM table1 
),
cte2 AS (
    SELECT [x], [highest_rank]=MAX([rank])
    FROM cte1
    GROUP BY [x]
)
SELECT cte1.[x],cte1.[y],cte1.[rank],cte2.[highest_rank]
FROM cte1 INNER JOIN cte2 ON cte2.[x]=cte1.[x];