Huzaifa M Aamir Huzaifa M Aamir - 4 months ago 8
SQL Question

How to query data within a group using sql server

I have a table that looks something like this which has been grouped and assigned a group number

Example:

groupnumber userid username borrower name
1 1 userA A
1 1 userA B
2 2 userB C
2 2 userB D
2 3 userC D


In this example I have shown group number 1 to be desired results. Each group should only have one user Id (multiple entries is fine) but can have different borrower names. <--- This group I want to keep

For group two where there are different userids within the group I need that group to be completely removed from the list or just queried so I can flag as conflicted data group

I got the first part of this set up so the groups are being shown but the second part I cant seem to figure out. Any help would be appreciate it

Basically I would like to write a query so that the table will yield this result

groupnumber userid username borrower name
1 1 userA A
1 1 userA B


Thus removing groupnumber 2 all together since the userid werent all the same in the group

Answer

Sorry that code got befluxtifitated when I posted it. I will try Ye Olde Markuppe Editorre to see if I can fix it:

DELETE FROM TABLE_NAME
WHERE GROUPNUMBER IN (
  SELECT GROUPNUMBER
  FROM TABLE_NAME
  WHERE COUNT( DISTINCT USERNAME ) > 1
)
Comments