CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2012
Leo Dan
Leo Dan

Reputation: 23

sql get duplicate column values grouped by another column

I've got the following temp table as an output from a query:

FacilityID      UserID   User_Name
1046            105      John Smith
1046            106      John Smith
1046            110      Jack Welsh 
1091            107      Ana Romero
1091            248      Rebecca Cruz 
1095            418      Alex Sterling

I need to display only these facilities that have users with the same name, and only these names should pass the query filter. This is to find out if any facility has users with exactly same name (even though these are different people). So, considering table above, I need to display only the following:

FacilityID      UserID   User_Name
1046            105      John Smith
1046            106      John Smith

Upvotes: 2

Views: 40

Answers (4)

Michał Turczyn
Michał Turczyn

Reputation: 37347

I'll chip in my solution:

select FacilityID, UserID, User_Name from (
    select FacilityID, UserID, User_Name
           count(*) over (partition by User_Name) cnt
    from MY_TABLE 
) a where cnt > 1

Upvotes: 0

ccarpenter32
ccarpenter32

Reputation: 1077

I would use the EXISTS clause:

(Example uses a CTE [TEMP] as a test)

;WITH TEMP (FacilityID, UserID, User_Name) AS (
    SELECT * FROM (
        VALUES
            ('1046','105','John Smith'), 
            ('1046','106','John Smith'), 
            ('1046','110','Jack Welsh'), 
            ('1091','107','Ana Romero'), 
            ('1091','248','Rebecca Cruz'), 
            ('1095','418','Alex Sterling')
    ) AS A (Column1, Column2, Column3)
)


SELECT TEMP.*
FROM TEMP
WHERE EXISTS (SELECT 1 
    FROM TEMP SubT 
    WHERE SubT.FACILITYID = TEMP.FACILITYID 
        AND SubT.USER_NAME = TEMP.USER_NAME 
        AND TEMP.USERID <> SubT.USERID
    )

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I would use exists :

select t.*
from table t
where exists (select 1 
              from table t1 
              where t1.FacilityID = t.FacilityID and 
                    t1.User_Name = t.User_Name and t.userid <> t1.userid
             );

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can use exists:

select t.*
from t
where exists (select 1
              from t t2
              where t2.FacilityID = t.FacilityID and t2.user_name = t.user_name and
                    t2.UserId <> t.userId and
             );

If you have a query returning results, then window functions are also a good choice:

with t as (<your query here>)
select t.*
from (select t.*, min(userid) over (partition by FacilityID, user_name) as min_ui,
             max(userid) over (partition by FacilityID, user_name) as max_ui
      from t
     ) t
where min_ui <> max_ui;

Upvotes: 2

Related Questions