How to apply DENSE_RANK() to records ordered by multiple columns?

Question

I'm writing a SQL stored procedure that for each AliasName it will retrieve me MasterNames it's associated with in the NameAssociations table, which contains AliasNameId, MasterNameId, and MatchRank, and StatusCode columns. I'll be using the stored procedure for server-side paging the data in C#, so I'd like the startRow endRow part to stay the same.

CREATE PROCEDURE [dbo].[NameAssociationsGetNameAssociations]
    @StartRow INT = 1,
    @EndRow INT = 1
AS
    WITH result_set AS (
        SELECT DENSE_RANK() OVER (ORDER BY an.Id) AS rowNum,        
        an.Id as AliasId, an.AliasName,
        mn.Id as MasterId, mn.MasterName, mn.CodeId, mn.RowUpdateVersion AS ConcurrencyToken, na.MatchRank
    FROM
        NameAssociations na 
        INNER JOIN AliasNames an
        ON na.AliasNameId = an.Id 
        INNER JOIN MasterNames mn
        ON na.MasterNameId = mn.Id 
    WHERE
        na.StatusCode = 0
    ) 
    SELECT
        rowNum, 
        AliasId as Id, AliasName,
        MasterId as Id, MasterName, CodeId, ConcurrencyToken, MatchRank
    FROM
        result_set
    WHERE
        rowNum BETWEEN @StartRow AND @EndRow

This procedure works and retrieves rows numbered correctly:

rowNum	Id	AliasName	Id	MasterName	CodeId	ConcurrencyToken	MatchRank
1	5	BName	34	SomeName1	2	0x0000021	1
2	6	DName	21	SomeName2	3	0x0000003	2
2	6	DName	2	SomeName3	1	0x00000A2	1
2	6	DName	40	SomeName4	1	0x00000B4	3
3	7	AName	11	SomeName5	1	0x000005B	1

So basically, I get a list of MasterNames for every AliasName.

But the rows are not ordered in the way I would like them to be, which is by AliasName ASC, Id (of alias) ASC, and MatchRank ASC, which would look like this:

rowNum	Id	AliasName	Id	MasterName	CodeId	ConcurrencyToken	MatchRank
1	7	AName	11	SomeName5	1	0x000005B	1
2	5	BName	34	SomeName1	2	0x0000021	1
3	6	DName	2	SomeName3	1	0x00000A2	1
3	6	DName	21	SomeName2	3	0x0000003	2
3	6	DName	40	SomeName4	1	0x00000B4	3

You can see the names of aliases are ordered alphabetically, and then the associated masters were reordered so the MatchRank is in ASC order. All of this while the rowNum is correct, which is what I've been having trouble with attaining.

I've tried doing PARTITION BY an.Id ORDER BY an.AliasName ASC, an.Id ASC, na.MatchRank ASC, but I must be misunderstanding what ORDER BY does in this case because the results come out wrong. The results look like it ordered the records only by MatchRank ASC and partitioned by it. I'm expecting it to order the data by those three columns, and then partition it by the an.Id.

How can I write the query so that the output result looks like the second example of table? I hope this was all clear.

`PARTITION BY` is basically a `GROUP BY` for aggregates, so the numbering restarts for every new `Id`. I think you want `ORDER BY AliasName, AliasId, MatchRank` with no `PARTITION`. The ordering of the actual output rows is dependent on an outer `ORDER BY` which you do not have, so theoretically the rows could come back in a different order from the rownumbering result. Re paging, you might find this interesting: https://stackoverflow.com/a/66484823/14868997 — Charlieface, Mar 05 '21 at 16:53
Your query and example output don't match up logically. The `partition by an.id` would restart the ranking for each new `id` value. I think you actually have an `order by` here? — iamdave, Mar 05 '21 at 16:57

score 1 · Answer 1 · answered Mar 05 '21 at 17:03

Ignoring what looks like a transcription/obfuscation error in your script that actually renders your SQL as invalid (dense_rank must have an order by), I think your solution is actually pretty simple.

The dense_rank function uses the values in the order by to determine which records get the same ranking value, so if you want to sort the output by the AliasName, but also want to make sure records with the same AliasName but different id values (not sure why this would be possible, but defensive coding is generally a good practice), you just need to include both values in your order by, in the order that you want to apply sorting. In your case this looks like the following minimal solution:

Query

declare @t table(id int, AliasName varchar(10), val int);
insert into @t values (1,'b',1),(2,'c',2),(2,'c',3),(2,'cc',4),(22,'c',5),(3,'a',6);

select *
      ,dense_rank() over (order by id) as rid
      ,dense_rank() over (order by AliasName, id) as rAliasName
from @t;

Output

id	AliasName	val	rid	rAliasName
3	a	6	3	1
1	b	1	1	2
2	c	2	2	3
2	c	3	2	3
22	c	5	4	4
2	cc	4	2	5

How to apply DENSE_RANK() to records ordered by multiple columns?

1 Answers1

Query

Output