Finding MAX rank when using DENSE_RANK to achieve incremental ranking

Question

Setup

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

Here is my query

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

The Resultset is looking good

id	item	subitem	toprow	Rank
1	item 1	NULL	1	1
2	item 2	SubItem 2-3	1	2
2	item 2	SubItem 2-2	2	2
2	item 2	SubItem 2-1	3	2
3	item 3	NULL	1	3
4	item 4	SubItem 4-3	1	4
4	item 4	SubItem 4-2	2	4
4	item 4	SubItem 4-1	3	4
5	item 5	NULL	1	5
6	item 6	SubItem 6-3	1	6

Is it possible to find what is the lowest rank without doing GROUP BY? I am OK if this query becomes inline view in the wrapping query.

Just use `DENSE_RANK() OVER (ORDER BY id)` instead of `RANK()` — GMB, Nov 01 '22 at 23:01
@GMB With this said, is there a way also to retrieve the MAX rank in the same query? Sort of like - look ahead. Or at least if this query becomes inline view then in the wrapping query?? — T.S., Nov 01 '22 at 23:39
Maybe you want `COUNT(*) OVER()`? That gives you the total count of records. It's pretty close to *the max of `RANK`*, but differs if there are ties in last position. — GMB, Nov 02 '22 at 00:15
@GMB I've been adding `COUNT(*) OVER()`. I will have to compare tomorrow the difference of count with `rank`. In my case 1 record will not make much difference. Thanks — T.S., Nov 02 '22 at 02:29
@GMB I reformatted my question since the original was an easy answer. And now I realized that I can't get close to the actualy number I need using `COUNT(*) OVER()` etc. FYI. Thanks — T.S., Nov 02 '22 at 15:33
Does https://stackoverflow.com/questions/11202878/partition-function-count-over-possible-using-distinct help? Maybe `dense_rank() over (ORDER BY id) + dense_rank() over (ORDER BY id desc) - 1`? — EdmCoff, Nov 02 '22 at 16:14
@EdmCoff This is interesting!! this returns me total number of ranks. True. But then I take 5000. And I need to find Max at this cut out. I am going to try additional layer of inline view. Work in Progress — T.S., Nov 02 '22 at 17:19
@EdmCoff I found the way and posted the answer. Your approach worked but it can be done by using `MAX(Rank) OVER ()`. Thank you — T.S., Nov 02 '22 at 21:56

T.S. · Accepted Answer · 2022-11-03T21:08:38.843

Here is the SQL that achieves exactly what I want

SELECT
    outer_table.*,
    /* additional subquery-based columns*/
FROM
(
    SELECT
        rank_table.*
        ,MAX(Rank) OVER () AS sys___MAX_RANK
    FROM
    (
        SELECT 
            Union_Tbl_Alias.*
            ,DENSE_RANK() OVER (ORDER BY id desc) 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 desc, subitem desc
        OFFSET 0 ROWS    -- required here 
        FETCH NEXT 5000 ROWS ONLY 
    ) rank_table
) outer_table    
where outer_table.Rank between 1 and 25
order by outer_table.Rank

DENSE_RANK() OVER (ORDER BY id desc) AS Rank in rank_table gets me the ranking over 5000 rows. And based on data this rank value will usually be less then 5000. MAX(Rank) OVER () AS sys___MAX_RANK gets me that number (max rank over number of records in fetch). Then in the outer_table I can simply get my page of data based on the rank and sort it by the rank, which efficient.

Basically, the motivation here is to achieve paging based on rank and not consecutive rows. I need to collate some of the values in the query, hence GROUP BY is not really possible. But if anyone has a better way, welcome to provide the answer.

Finding MAX rank when using DENSE_RANK to achieve incremental ranking

1 Answers1