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

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