SQL query to fetch output based on multiple row values

Question

I have table with sample data as follows

-----------------------------
UserID   | ScreenID | rights |
------------------------------
 admin        22         0
 admin        23         0
 admin        24         0

 userA        22         1
 userA        23         1
 userA        24         1

 userB        22         0
 userB        23         0
 userB        24         1

Now I want to fetch 'UserID' and 'rights' details as follows: For each user if the "rights" for all the ScreenIDs are same then that rights value (either '0' or '1') else rights value as 2

That means, required output from the above table should be:

admin , 0
userA , 1
userB  ,2

Please help me in writing SQL query for the same. Thanks.

StuartLC · Accepted Answer · 2014-06-06 04:50:08Z

5

With a cte or derived table which does a GROUP BY on UserId, you can then tell whether all Rights are the same:

SELECT UserID,
       CASE 
          WHEN minR = maxR THEN minR
          ELSE 2
       END AS Result
FROM
(
  SELECT UserID, MIN(Rights) as minR, MAX(Rights) as maxR
  FROM UserRights
  GROUP BY UserID
) x;

SqlFiddle here

edited Jun 6, 2014 at 4:50

answered Jun 6, 2014 at 4:41

StuartLC

108k18 gold badges225 silver badges295 bronze badges

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

Comments

Lennart - Slava Ukraini · Accepted Answer · 2014-06-06 04:44:38Z

2

Something like (untested on sqlserver):

select userid
     , case when min(rights) = max(rights) 
            then min(rights) 
            else 2 
       end 
from t 
group by userid

answered Jun 6, 2014 at 4:44

Lennart - Slava Ukraini

7,2111 gold badge23 silver badges37 bronze badges

Collectives™ on Stack Overflow

SQL query to fetch output based on multiple row values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related