September 27, 2016 at 8:33 am
I have a feeling this might end up being a simple solution but I'm spinning my wheels on it and it is driving me crazy. Here are some dummy tables I'm dealing with:
NameFrequency
NameRoleCount
JackStudent3
JackTeacher5
JackAdmin2
JackStudent2
SueStudent10
SueTeacher4
RoleRankLookup
RolePriorityRank
Teacher1
Student2
Admin3
The goal is that I need to select the grouping of each Name and Role that have the highest counts. In the case of two groupings having the same counts, I would then need to select the Role that has the highest (lowest actual number) PriorityRank in the RoleRankLookup table. The part I have figured out is selecting the max value using this
;WITH SumCount
AS (
SELECT NAME
,[Role]
,sum([Count]) AS SumOfCount
FROM NameFrequency
GROUP BY NAME
,[Role]
)
,MaxCount
AS (
SELECT *
,ROW_NUMBER() OVER (
PARTITION BY NAME ORDER BY SumOfCount DESC
) RN
FROM SumCount
)
SELECT NAME
,[Role]
,SumOfCount
FROM MaxCount
WHERE RN = 1
However, the result is only half correct because of the way the results are ordered in the MaxCount CTE. It returns the results
NameRoleSumOfCount
JackStudent5
SueStudent10
The Sue Student = 10 is correct because that is the max value. However, Jack Student 5 is not the value I was looking for because both Student and Teacher have a count of 5 and I need it to select Teacher based on the PriorityRank. How can I add that check to my existing code so that it returns Jack Teacher = 5? Here is the full list of results that show the RN values in case it is of any help
NameRoleSumOfCountRN
JackStudent51
JackTeacher52
JackAdmin23
SueStudent101
SueTeacher42
September 27, 2016 at 9:01 am
;WITH SumCount
AS (
SELECT NF.NAME
,NF.[Role]
,RRL.PriorityRank
,sum([Count]) AS SumOfCount
FROM NameFrequency NF inner join RoleRankLookup RRL on RRL.[role]=NF.Role
GROUP BY NAME
,[Role]
,PriorityRank
)
,MaxCount
AS (
SELECT *
,ROW_NUMBER() OVER (
PARTITION BY NAME ORDER BY SumOfCount DESC,PriorityRank asc
) RN
FROM SumCount
)
SELECT NAME
,[Role]
,SumOfCount
FROM MaxCount
WHERE RN = 1
September 27, 2016 at 9:07 am
WITH TEMP_CTE AS(
SELECT NameFrequency.Name, NameFrequency.Role, RoleRankLookup.Priority, SUM(NameFrequency.Count) AS TOTAL, RANK() OVER(PARTITION BY NameFrequency.Name ORDER BY SUM(NameFrequency.Count) DESC, RoleRankLookup.Priority ASC) AS RN
FROM (VALUES('Jack', 'Student', 3),
('Jack', 'Teacher', 5),
('Jack', 'Admin', 2),
('Jack', 'Student', 2),
('Sue', 'Student', 10),
('Sue', 'Teacher', 3) )
AS NameFrequency(Name, Role, Count),
(VALUES ('Teacher', 1),
('Student', 2),
('Admin', 3))
AS RoleRankLookup(Role, Priority)
WHERE
NameFrequency.Role = RoleRankLookup.Role
GROUP BY NameFrequency.Name, NameFrequency.Role, RoleRankLookup.Priority
)
SELECT * FROM TEMP_CTE WHERE RN = 1
With no nested CTE.
September 27, 2016 at 9:45 am
Thanks, MadAdmin and ZZ!
September 27, 2016 at 9:48 am
ZZartin (9/27/2016)
WITH TEMP_CTE AS(SELECT NameFrequency.Name, NameFrequency.Role, RoleRankLookup.Priority, SUM(NameFrequency.Count) AS TOTAL, RANK() OVER(PARTITION BY NameFrequency.Name ORDER BY SUM(NameFrequency.Count) DESC, RoleRankLookup.Priority ASC) AS RN
FROM (VALUES('Jack', 'Student', 3),
('Jack', 'Teacher', 5),
('Jack', 'Admin', 2),
('Jack', 'Student', 2),
('Sue', 'Student', 10),
('Sue', 'Teacher', 3) )
AS NameFrequency(Name, Role, Count),
(VALUES ('Teacher', 1),
('Student', 2),
('Admin', 3))
AS RoleRankLookup(Role, Priority)
WHERE
NameFrequency.Role = RoleRankLookup.Role
GROUP BY NameFrequency.Name, NameFrequency.Role, RoleRankLookup.Priority
)
SELECT * FROM TEMP_CTE WHERE RN = 1
With no nested CTE.
Why are you STILL using old style joins? They've been deprecated for how long?
Also, both pieces of code use the exact same plan.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply