SQL with having statement now want complete rows-CodePudding

Here is a mock table

MYTABLE ROWS
PKEY    1,2,3,4,5,6
COL1    a,b,b,c,d,d
COL2    55,44,33,88,22,33

I want to know which rows have duplicated COL1 values:

select col1, count(*)  
from MYTABLE
group by col1
having count(*) > 1

This returns :

b,2
d,2

I now want all the rows that contain b and d. Normally, I would use where in stmt, but with the count column, not certain what type of statement I should use?

CodePudding user response：

maybe you need

select * from MYTABLE
where col1 in 
(
select col1 
from MYTABLE
group by col1
having count(*) > 1
)

CodePudding user response：

Use a CTE and a windowed aggregate:

WITH CTE AS(
    SELECT Pkey,
           Col1,
           Col2,
           COUNT(1) OVER (PARTITION BY Col1) AS C
    FROM dbo.YourTable)
SELECT PKey,
       Col1,
       Col2
FROM CTE
WHERE C > 1;

CodePudding user response：

Lots of ways to solve this here's another

select * from MYTABLE
join
(
select col1 ,count(*)
from MYTABLE
group by col1
having count(*) > 1
) s on s.col1 = mytable.col1;