使用DENSE_RANK实现递增排名时，如何无需GROUP BY获取最大排名？

go
create table eidmadm.PerfData1 (id int primary key, c1 varchar(100))
create table eidmadm.PerfData2 (id int identity primary key, fkId int, c1 varchar(100), FOREIGN KEY (fkId) REFERENCES eidmadm.PerfData1(id))

go

DECLARE @id INTEGER = 0;


begin 
    WHILE @id <= 10000
    BEGIN

        SET @id = @id + 1; 

        insert into eidmadm.PerfData1 (id, c1) values (@id, concat( 'item ', @id) );
        
        if @id % 2 = 0
        begin
            insert into eidmadm.PerfData2 (fkId, c1) values (@id, concat( 'SubItem ', @id, '-', 1) );
            insert into eidmadm.PerfData2 (fkId, c1) values (@id, concat( 'SubItem ', @id, '-', 2) );
            insert into eidmadm.PerfData2 (fkId, c1) values (@id, concat( 'SubItem ', @id, '-', 3) );
        end;

    END;

end;

go 

SELECT
    Union_Tbl_Alias.*,
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) toprow,
    --RANK() OVER (PARTITION BY id ORDER BY id, subitem desc) AS Rank  
    DENSE_RANK() OVER (ORDER BY id) AS Rank
FROM
(
    SELECT 
        eidmadm.PerfData1.id,
        eidmadm.PerfData1.c1 item,
        eidmadm.PerfData2.c1 subitem--,
    FROM 
        eidmadm.PerfData1 inner join 
        eidmadm.PerfData2 on eidmadm.PerfData1.id = eidmadm.PerfData2.fkId
    WHERE (1 = 1) 

    UNION

    SELECT 
        eidmadm.PerfData1.id,
        eidmadm.PerfData1.c1 item,
        eidmadm.PerfData2.c1 subitem--,
    FROM 
        eidmadm.PerfData1 left join 
        eidmadm.PerfData2 on eidmadm.PerfData1.id = eidmadm.PerfData2.fkId
    WHERE (1 = 1) and eidmadm.PerfData2.fkId is NULL

) Union_Tbl_Alias
ORDER BY
    id, subitem desc
OFFSET 0 ROWS    
FETCH NEXT 5000 ROWS ONLY 

可以做到不用GROUP BY就能获取结果中的最大Rank值，以下是两种可行方案：

方案1：窗口聚合函数（保留原结果并附加最大Rank） #

将原查询作为内联视图嵌套，在外层使用MAX(Rank) OVER ()窗口函数获取全局最大Rank值，该函数会在每一行返回相同的最大Rank：

SELECT
    *,
    MAX(Rank) OVER () AS MaxRank
FROM (
    -- 原查询内容
    SELECT
        Union_Tbl_Alias.*,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) toprow,
        DENSE_RANK() OVER (ORDER BY id) AS Rank
    FROM
    (
        SELECT 
            eidmadm.PerfData1.id,
            eidmadm.PerfData1.c1 item,
            eidmadm.PerfData2.c1 subitem
        FROM 
            eidmadm.PerfData1 inner join 
            eidmadm.PerfData2 on eidmadm.PerfData1.id = eidmadm.PerfData2.fkId
        WHERE (1 = 1) 

        UNION

        SELECT 
            eidmadm.PerfData1.id,
            eidmadm.PerfData1.c1 item,
            eidmadm.PerfData2.c1 subitem
        FROM 
            eidmadm.PerfData1 left join 
            eidmadm.PerfData2 on eidmadm.PerfData1.id = eidmadm.PerfData2.fkId
        WHERE (1 = 1) and eidmadm.PerfData2.fkId is NULL

    ) Union_Tbl_Alias
    ORDER BY
        id, subitem desc
    OFFSET 0 ROWS    
    FETCH NEXT 5000 ROWS ONLY 
) AS InnerQuery

方案2：直接聚合子查询结果（仅获取最大Rank） #

如果只需要单独的最大Rank值，直接对子查询的Rank列做聚合查询即可，无需GROUP BY：

SELECT MAX(Rank) AS MaxRank
FROM (
    -- 原查询内容
    SELECT
        Union_Tbl_Alias.*,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) toprow,
        DENSE_RANK() OVER (ORDER BY id) AS Rank
    FROM
    (
        SELECT 
            eidmadm.PerfData1.id,
            eidmadm.PerfData1.c1 item,
            eidmadm.PerfData2.c1 subitem
        FROM 
            eidmadm.PerfData1 inner join 
            eidmadm.PerfData2 on eidmadm.PerfData1.id = eidmadm.PerfData2.fkId
        WHERE (1 = 1) 

        UNION

        SELECT 
            eidmadm.PerfData1.id,
            eidmadm.PerfData1.c1 item,
            eidmadm.PerfData2.c1 subitem
        FROM 
            eidmadm.PerfData1 left join 
            eidmadm.PerfData2 on eidmadm.PerfData1.id = eidmadm.PerfData2.fkId
        WHERE (1 = 1) and eidmadm.PerfData2.fkId is NULL

    ) Union_Tbl_Alias
    ORDER BY
        id, subitem desc
    OFFSET 0 ROWS    
    FETCH NEXT 5000 ROWS ONLY 
) AS InnerQuery

额外说明 #

由于你的DENSE_RANK()是按id排序生成，且每个id对应唯一的Rank值，最大Rank实际上等于PerfData1表中的最大id值，也可以直接通过SELECT MAX(id) FROM eidmadm.PerfData1得到结果，但上述两种方案更通用，适用于Rank生成逻辑更复杂的场景。

内容的提问来源于stack exchange，提问作者T.S.