Fetching Data Anomalies in SQL (2 Columns)-CodePudding

I have a table with two columns and following data

ID NAME
1  ALPHA
1  ALPHA
2  BETA
1  BETA

First three rows are correct data, but in the last row someone accidentally entered ID 1 instead of ID 2, can anyone help me with a query to fetch multiple rows of ID for distinct names. I have tried the query below but its not yielding the correct result

SELECT F1.ID FROM myTable F1 WHERE F1.Name in (SELECT DISTINCT F2.Name FROM myTable F2)

CodePudding user response：

Actually, you need names that have multiple IDs; right?

For sample data:

SQL> select * From test;

        ID NAME
---------- -----
         1 alpha
         1 alpha
         2 beta
         1 beta

Query, using group by and having clauses:

SQL> select name
  2  from test
  3  group by name
  4  having count(distinct id) > 1;

NAME
-----
beta

SQL>