T-SQL query to select only rows which do not have a unique value in specific column

Question

I have a table in SQLServer with the following three columns:

RowID is unique but RMA and Value are not.

I want to write a query that selects all rows from the table which share any particular RMA. So let's say we have the following three rows

ID RMA Value 1 - 222- Car 2 - 923 - Boat 3 - 222 - Plane 4 - 555 - Other 5 - 555 - Jet

I want a query that will return rows 1, 3, 4 and 5. Row 2 will not be selected because its RMA only occurs once.

I don't even know where to start for this. Do I need to use the COUNT() function?

Andrey Korneyev · Accepted Answer · 2016-06-22 13:39:24Z

You can use something like this:

select * from your_Table where RMA in ( select RMA from your_table group by RMA having count(RowID) > 1 )

Inner query gives you RMAs what occurs more than once, and outer gives records having these RMAs.

cableload · Accepted Answer · 2016-06-22 13:39:35Z

Try something like this

select id,rma,value from yourtable where rma in (select RMA from yourtable group by RMA having count(*)>1)

sgeddes · Accepted Answer · 2016-06-22 13:40:48Z

Here's another approach with exists that doesn't require aggregation:

select * from yourtable y where exists ( select 1 from yourtable y2 where y.rma = y2.rma and y.id != y2.id )

gofr1 · Accepted Answer · 2016-06-22 13:43:05Z

SELECT RowID, RMA, [Value] FROM ( SELECT RowID, RMA, [Value], COUNT(*) OVER (PARTITION BY RMA ORDER BY RMA) as c FROM YourTable ) as p WHERE p.c > 1

Output:

RowID RMA Value 3 222 Plane 1 222 Car 4 555 Other 5 555 Jet

ɐlǝx · Accepted Answer · 2016-06-22 13:41:54Z

SELECT * FROM TABLE WHERE RMA IN (SELECT RMA FROM TABLE GROUP BY RMA HAVING COUNT(*) >1)

You can use a nested query that consider only the row with COUNT of RMA > 1

5 Answers 5