September 10, 2013 at 12:42 am
create table Sample
(Name Varchar(100),
Role Varchar(10))
insert into Sample values ('Vignesh' , 'Admin')
insert into Sample values ('Vignesh' , 'User')
insert into Sample values ('Bala' , 'Admin')
insert into Sample values ('Bala' , 'User')
insert into Sample values ('Suresh' , 'Admin')
insert into Sample values ('Arun' , 'User')
1. In sample table there were 4 names Vignesh, Bala, arun & suresh
2. There are 2 kinds of role (admin & user)
3. Vignesh & bala have both the roles , arun & suresh have any one of the role
I need to find who are having both roles ..
Kindly help..
September 10, 2013 at 12:59 am
This should do the trick:
SELECT Name
FROM SAMPLE
GROUP BY Name
HAVING COUNT(*) > 1;
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 10, 2013 at 1:23 am
Koen's query is much easier, but here's my alternate query anyway.
WITH cteRoles
AS
(
SELECTROW_NUMBER() OVER (PARTITION BY Name ORDER BY NAME) RowNr,
Name,
Role
FROMSample
)
SELECTName
FROMcteRoles
WHERERowNr = 2
September 10, 2013 at 1:25 am
September 10, 2013 at 1:36 am
If there are double rows (like the same NAME is entered twice both with the ROLE 'User') the solution of Koen will give false results. The code below will just display the results where a NAME is entered only once as 'Admin' and once as 'User'.
select name
from Sample
group by name
having sum(case when ROLE = 'Admin' then 1 else 0 end) = 1
and sum(case when ROLE = 'User' then 1 else 0 end) = 1
September 10, 2013 at 1:47 am
HanShi (9/10/2013)
If there are double rows (like the same NAME is entered twice both with the ROLE 'User') the solution of Koen will give false results. The code below will just display the results where a NAME is entered only once as 'Admin' and once as 'User'.
select namefrom Sample
group by name
having sum(case when ROLE = 'Admin' then 1 else 0 end) = 1
and sum(case when ROLE = 'User' then 1 else 0 end) = 1
Pffff, crap in crap out 😀
I believe this to be a more elegant solution:
SELECT Name
FROM [SAMPLE]
GROUP BY Name
HAVING COUNT(DISTINCT [Role]) > 1;
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 10, 2013 at 1:55 am
Koen Verbeeck (9/10/2013)
Pffff, crap in crap out 😀
I totally agree!! 😎
But with such a limited sample you better prepare for the worst. Or at least provide different solutions. The OP can pick the one that fits the best. And he's the only one who can decide which one that is.
September 10, 2013 at 2:13 am
+1 to koen ... 🙂
September 10, 2013 at 2:28 am
drop table #Sample
create table #Sample(Name Varchar(100),Role Varchar(10))
insert into #Sample values ('Vignesh' , 'Admin')
insert into #Sample values ('Vignesh' , 'User')
insert into #Sample values ('Bala' , 'Admin')
insert into #Sample values ('Bala' , 'User')
insert into #Sample values ('Suresh' , 'Admin')
insert into #Sample values ('Arun' , 'User')
insert into #Sample values ('Suresh' , 'Admin')
insert into #Sample values ('Arun' , 'User')
-- user has two or more rows in the table - may be same role
SELECT Name
FROM #SAMPLE
GROUP BY Name
HAVING COUNT(*) > 1;
-- user has two or more rows in the table - may be same role
WITH cteRoles
AS
(
SELECTROW_NUMBER() OVER (PARTITION BY Name ORDER BY NAME) RowNr,
Name,
Role
FROM#Sample
)
SELECTName
FROMcteRoles
WHERERowNr = 2
-- user has any two or more roles
SELECT s.Name
FROM #Sample s
WHERE EXISTS (
SELECT 1
FROM #Sample i
WHERE i.Name = s.Name
AND i.[Role] <> s.[Role]
)
-- user has two or more roles, including both Admin and User
SELECT Name
FROM (
SELECT Name, [Role]
FROM #Sample s
WHERE [Role] IN ('Admin','User')
GROUP BY Name, [Role]
) d
GROUP BY Name
HAVING COUNT(*) > 1
-- user has two or more roles, including both Admin and User
SELECT s.Name
FROM #Sample s
WHERE EXISTS (
SELECT 1
FROM #Sample i
WHERE i.Name = s.Name
AND i.[Role] = 'User'
)
AND s.[Role] = 'Admin'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 10, 2013 at 3:37 am
Thanks everybody...
It helps me a lot .
September 10, 2013 at 11:22 am
For those using ROW_NUMBER, if you want users having 2 OR MORE roles, shouldn't the filter be rownum >= 2 instead of just rownum = 2?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 13, 2013 at 5:45 am
To avoid the duplicate results use rank().
WITH CTEROLES
AS(
SELECT Name,Role,RANK() OVER( PARTITION BY Name ORDER BY Role) AS NumberofRoles FROM Sample
)
SELECT Name,NumberofRoles FROM CTEROLES WHERE NumberofRoles>1
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply