Bad_Student Posted October 10, 2019 Share Posted October 10, 2019 I have to get the ID if 2 or more IDs have the same a and b values columns - ID, a, b Data ID 1,2,3,4,5 a 1,1,2, 0,0 b 1,2,3,1,1 Expecting output ID - 4,5 Thanks Link to comment Share on other sites More sharing options...
niche Posted October 18, 2019 Share Posted October 18, 2019 (edited) Looks like ID 1 is the ID that has the same a/b values. How does ID 4 & 5 qualify? Edited October 18, 2019 by niche Link to comment Share on other sites More sharing options...
Sherin Posted November 21, 2019 Share Posted November 21, 2019 Try either of them select t1.Id,t1.a,t1.b from Table1 t1 inner join (select a,b,count(*) as num from Table1 group by a,b having count(*)>1) t2 on t1.A=t2.A and t1.B=t2.b select t1.Id,t1.a,t1.b from Table1 t1 inner join (select a,b,ROW_NUMBER() OVER (PARTITION BY a,b ORDER BY a,b) as num from Table1 group by a,b having count(*)>1) t2 on t1.A=t2.A and t1.B=t2.b Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now