sql using count & group by without using distinct keyword?

Question

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.

I don't understand what the problem is. Why doesn't select count(), or select count(distinct u.UserId) meet your needs? — paul
– paul, Commented Jul 20, 2012 at 9:44
in my function i replace select * from table_name to select count () as count_var from table_name. so where ever select distinct * from is used it gets replaced to select count () so count includes duplicate records — suganya
– suganya, Commented Jul 20, 2012 at 10:03
since its core module i must not change the functions in it. thats the problem :-( — suganya
– suganya, Commented Jul 20, 2012 at 10:05

GarethD · Accepted Answer · 2012-07-20 10:19:07Z

2

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

edited Jul 20, 2012 at 10:19

answered Jul 20, 2012 at 9:45

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

suganya Over a year ago

it works but wat the problem is select distinct in sub query also gets replaced to select count (*) which causes an error.

Collectives™ on Stack Overflow

sql using count & group by without using distinct keyword?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related