Oracle SQL - How to Filter Out Duplicate Row Based on 1 Column-CodePudding

So I've Been trying to only get a unique value for 1 column. For Example I have a Table called "TBL"

COL_A   COL_B     COL_C
1        "HAT"     "RED"
2        "HAT"     "BLUE"
3        "SHIRT"   "BLUE"
3        "SHOES"   "GREEN"

I want to get the table to filter out all the duplicates for COL_A so the end result table would look like this - getting rid of 2 rows that have the duplicate 3 IDs. But still keeping all the columns just only filtering out the duplicates for one column.

COL_A   COL_B   COL_C
1        "HAT"   "RED"
2        "HAT"   "BLUE

I've tried multiple ways, first with the DISTINCT selector but after digging there still is a row left with the ID and I have also tried to use the GROUP BY selector and it also comes back with 1 row. If I can create a table that only has the unique values for COL_A then I can join that with my other table to only grab all the other columns but the table will only have the unique values for COL_A.

The statements I've tried were

SELECT DISTINCT COL_A FROM TBL 
SELECT COL_A FROM TBL GROUP BY COL_A

I would think that one of these statements would be able to give me my result but instead it returns 3 even though the 3 id has 2 rows. If I could get it to return only 1 and 2 I can join that with another table to get all the other data for the unique IDs

COL_A
 1
 2
 3

Any suggestions?

CodePudding user response：

You can go with A IN clause and a Sub-SELECT

SELECT "COL_A", "COL_B", "COL_C" FROM tabl1
WHERE "COL_B" IN (
SELECT 
 "COL_B"
FROM tabl1
GROUP BY "COL_B"
 HAVING COUNT("COL_B") > 1 
 AND COUNT(DISTINCT "COL_C") > 1)
 ORDER BY "COL_A"

COL_A | COL_B | COL_C
----: | :---- | :----
    1 | HAT   | RED  
    2 | HAT   | "BLUE

db<>fiddle here

CodePudding user response：

You can use NOT IN to find the ones that are not repeated. For example:

select *
from t
where col_a not in (
  select col_a from t group by col_a having count(*) > 1
)

Result:

 COL_A  COL_B  COL_C 
 ------ ------ ----- 
 1      HAT    RED   
 2      HAT    BLUE

See example at db<>fiddle.