GroupBy/Having Question

  • 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

  • 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

  • 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"

  • 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