July 2, 2007 at 5:43 pm
I think this is a group by/having query, but I'm having a hard time getting the correct results. I have a table that has a UserID, Privilege, TargetID, Permission (value can be 1, 2 or 3), and Strength (value can be 1-200).
One privilege can have multiple records with different strengths and permissions for a TargetID. I need to return the highest strength for that Privilege and TargetID unless there is a tie in a strength for the same Privilege and TargetID, then I need to select the highest permission.
When I execute the following query, it's returning the results below:
SELECT p.Privilege, up.TargetPersistID, up.Permission, up.Strength
FROM tblUserPrivileges as up
INNER JOIN tblPrivileges as p on up.PrivilegeID = p.PrivilegeID
WHERE UserID = @user-id
GROUP BY p.Privilege, up.Strength, up.Permission, up.TargetPersistID
ORDER BY p.Privilege, up.Strength desc, up.Permission desc
Privilege, TargetID, Permission,Strength
ViewContent, 81883, 2, 10
AddGroups, 84524, 2, 50
AddGroups, 84524,3, 15
ViewGroups, 84524, 2, 10
ViewRoles, 84524,2,10
AddRoles, 84524,2,10
RmvPrivilege, 84524,3,10
RmvPrivilege ,84524, 2, 10
UsePage, 81883,3,50
UsePage,81883,2,10
But what I really need is just the first AddGroups, RmvPrivilege, and UsePage as well as the other records. Can someone please give me some advice?
Thanks!
Wendy Schuman
July 3, 2007 at 1:37 pm
In order to get the results I needed, I ended up using a temp table for this. First I used a subquery to get the max strength and inserted those results into a temp table. Then I selected from the temp table to get max Permission.
Wendy Schuman
July 3, 2007 at 4:25 pm
Use the new SQL Server 2005 features!
SELECT
Privilege,
TargetID,
Permission,
Strength
FROM (
SELECT Privilege,
TargetID,
Permission,
Strength,
ROW_NUMBER() OVER (PARTITION BY Privilege, TargetID ORDER BY Strength DESC, Permission DESC) AS RecID
FROM Table1
) AS d
WHERE RecID = 1
N 56°04'39.16"
E 12°55'05.25"
August 3, 2007 at 12:49 pm
Hi Peter,
I'm sorry I didn't get back to you about this sooner. I got the stored procedure working with my temp tables and thought I would come back to it to make it more efficient when I had time. I wanted to be sure that I could implement it and understand what it was doing. It works great! Thanks so much! I feel much better not using temp tables in the stored procedure.
Wendy Schuman
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply