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
CodePudding user response:
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