Fetching multiple rows based on grouping

Question

Consider this data

PK field1 field2
1  a      b
2  a      (null)
3  x      y
4  x      z
5  q      w

I need to get this data

select all columns from all rows where field1 has count >1

Which means the desired output is

PK field1 field2
1  a      b
2  a      (null)
3  x      y
4  x      z

i tried and finally settled for

select * from mytable  where field1 in 
(select field1 from mytable group by field1 having count(field1)>1 ) order by field1

but there has to be a better way than this

Answer 1

That's the way I would do it.

You could rewrite it with a join to the subquery instead of using in, but I doubt it would be any faster.

Edit: Ok, so for reference, the "join" method would go something like this:

select m.* from mytable m 
join (
  select field1 from mytable 
  group by field1 
  having count(field1)>1 
) j on m.field1=j.field1
order by m.field1

And it seems it's worth testing to see if it's faster (thanks @binaryLV).

Answer 2

Another way, if using T-SQL

;WITH T AS
(
 SELECT PK, FIELD1, FIELD2, COUNT(FIELD1) OVER(PARTITION BY FIELD1) AS R
)
SELECT PK, FIELD1, FIELD2
FROM T
WHERE R > 1