sql get duplicate column values grouped by another column

sql query to find duplicate records in a column
find duplicate rows in sql with multiple columns
sql select duplicate rows based on one column
how to find duplicate records in sql without group by
sql query to find duplicate rows in a table
sql select rows with same values in two column
sql sum group by multiple columns
how to find duplicate records in oracle

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


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
             );

SQL DISTINCT: Removing Duplicates In a Result Set Examples, How do I check if a column contains duplicates in SQL? Here I show how to find duplicates and their frequency among multiple columns using the GROUP BY clause. Suppose you have data in which you need to find the duplicates in a single column (for example to find common names in a list of names) or in multiple columns (for example to find all the persons who have same name and age but reside at a different address).


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;

Listing Duplicate Values by Group, How do I select a record without duplicates in one column in SQL? Find duplicates where another column have different columns Tag: sql , sql-server I need to update records that will match a specific query, so I'm currently trying to figure out how to find a list of duplicate values where one column differs in value.


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
    )

Finding Duplicate Rows in SQL Server, How do I duplicate a row in SQL by group? The initial SELECT simply selects every column in the users table, and then inner joins it with the duplicated data table from our initial query. Because we’re joining the table to itself, it’s necessary to use aliases (here, we’re using a and b) to label the two versions.


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

Counting duplicate instances within column in SQL when grouping , An easy way to find duplicate values in a table is to group the results according to the field that you're looking at Grouping by Another Column. As you can see in the above output, the rows with duplicate NAMEs are grouped under same NAME and their corresponding SALARY is the sum of the SALARY of duplicate rows. The SUM() function of SQL is used here to calculate the sum. Group By multiple columns: Group by multiple column is say for example, GROUP BY column1, column2.


How to Find Duplicate Records in Oracle, First, create a new table named t1 that contains three columns id , a , and b . the GROUP BY clause to find the duplicate rows in both a and b columns of the t1​  Locating duplicate rows based on multiple column values with SQL turns out to be a fairly simple exercise. If we take the usual method for locating duplicate rows based on a single column’s value, we see that locating a duplicate is nothing more than grouping on that column where the instance of that column value appears more than once.


How To Find Duplicate Values in MySQL, This should do what you require (fiddle): Create table j (tid serial, nom text, jid text​); Insert into j (nom, mid) values ('Sam', 'a'), ('Sam', 'b'), ('Ali',  Generally, the query for finding the duplicate values in one column using the GROUP BY clause is as follows: SELECT col , COUNT ( col ) FROM table_name GROUP BY col HAVING COUNT ( col ) > 1 ;


SQLite COUNT Function: Count Items In A Group, Home / Advanced Oracle SQL / How to Find Duplicate Records in Oracle Summary: in this tutorial, you will learn how to find duplicate records in the Oracle statement creates a new table named fruits that consists of three columns: you first list the fruit name and color columns in both SELECT and GROUP BY clauses. Using standard SQL on most RDBMS, there are various ways. Using a subquery: SELECT d.dept, d.role1, d.role2, DEF FROM data d INNER JOIN ( SELECT dept, role1, role2 FROM data GROUP BY dept, role1, role2 HAVING COUNT(distinct DEF) > 1 ) dup ON dup.dept = d.dept AND dup.role1 = d.role1 AND dup.role2 = d.role2 ;