Dense_Rank does not work as it is expected

October 15, 2022

I’m creating a query where I need to get the ranking depending on how many are duplicated in the column "MacAddress"(amount) in the table MacsUsers, this is the data:

this is my SQL query:

SELECT
COUNT([MU].MacAddress) AS Quantity,
[USER].Name,
[USER].SurName,
[MU].MacAddress,
DENSE_RANK() OVER(ORDER BY mu.MacAddress) AS RNK

FROM MacsUsers [MU]
JOIN Macs [MAC] ON [MAC].MacAddress = [MU].MacAddress
JOIN Users [USER] ON [MAC].UserEmail = [USER].Email
JOIN Profiles [PROFILE] ON [PROFILE].MacAddress = [MAC].MacAddress

GROUP BY mu.MacAddress,[USER].Name,
[USER].SurName

I think I’m doing well with the DENSE_RANK function but it does not work as it is expected, any possible solution? thanks advance

>Solution :

Your DENSE_RANK window function should apply ordering on the "Quantity" field instead of the "MacAddress" field. In order to apply a window function on an aggregated field, you need to have a subquery as follows.

WITH cte AS (
    SELECT COUNT([MU].MacAddress) AS Quantity,
           [USER].Name,
           [USER].SurName,
           [MU].MacAddress
    FROM MacsUsers [MU]
    JOIN Macs [MAC] ON [MAC].MacAddress = [MU].MacAddress
    JOIN Users [USER] ON [MAC].UserEmail = [USER].Email
    JOIN Profiles [PROFILE] ON [PROFILE].MacAddress = [MAC].MacAddress
    GROUP BY mu.MacAddress,
             [USER].Name,
             [USER].SurName
) 
SELECT *, DENSE_RANK() OVER(ORDER BY Quantity) AS RNK 
FROM cte