CODEWITHSUNDEEP
sqlcountgroup-bydistinctmultiple-tables
suganya
suganya

Reputation: 245

sql using count & group by without using distinct keyword?

I want to optimize this query 

    **SELECT * FROM Users WHERE Active = 1 AND UserId IN (SELECT UserId FROM Users_Roles WHERE RoleId IN (SELECT RoleId FROM Roles WHERE PermissionLevel >= 100)) ORDER BY LastName**

execution time became less wen i replace above query with joins as below, 

     **SELECT  u.* FROM Users u INNER JOIN Users_Roles ur ON (u.UserId = ur.UserId) INNER JOIN Roles r ON  (r.RoleId = ur.RoleId) WHERE u.Active = 1  AND r.PermissionLevel > 100 GROUP BY u.UserId ORDER BY u.LastName**

But the above query gives duplicate records since my roles table has more than one entry for every user.

I cant use DISTINCT since there is a function where i find count by replacing SELECT(*) FROM to SELECT COUNT(*) FROM to find count for pagination and then execute count query and result query

As we already known that count & GROUP BY is used together will result in bad output.

Now i want to optimize the query and have to find number of rows ie count for the query. Please give be better way find out the result.

Upvotes: 1

Views: 1753

Answers (1)

GarethD
GarethD

Reputation: 69799

It is difficult to optimise other peoples queries without fully knowing the schema, what is indexed what isn't, how much data there is, what your DBMS is etc. Even with this we can't see execution plans, IO statistics etc. With this in mind, the below may not be better than what you already have, but it is how I would write the query in your situation.

SELECT  u.* 
FROM    Users u
        INNER JOIN 
        (   SELECT  ur.UserID
            FROM    Users_Roles ur
                    INNER JOIN Roles r
                        ON r.RoleID = ur.RoleID
            WHERE   r.PermissionLevel > 100 
            GROUP BY ur.UserID
        ) ur
            ON u.UserId = ur.UserId
WHERE   u.Active = 1  
ORDER BY u.LastName

Upvotes: 2

Related Questions