sql server - Non Aggregate fields in MS SQL

Question

I have MS SQL server table which contains the data as follows

id  val1    val2  
1   100.00  50.00
2   25.00   30.00
3   30.00   25.00
4   100.00  50.00
5   40.00   80.00
6   25.00   30.00
7   80.00   21.00
8   25.00   30.00

In the above table, few val1 val2 values combination occurs more than ones i.e. 100.00 50.00 is occurred twice, 25.00 30.00 occurred thrice. Likewise, if any combinations occurred more than ones, I would need to get those Ids. So my result would be id - 1,2,4,6,8. please help how to query this in MS SQL. Thanks

Answer 1

The CTE obtains the val1, val2 pairs that occur more than once. The we join with the table to get the id values:

;with cte as (
   select val1, val2 
   from t 
   group by val1, val2 
   having count(*) > 1
)
select id
from t
join cte on t.val1 = cte.val1 and t.val2 = cte.val2