Create a group indicator (SQL)
- by user1723699
I am looking to create a group indicator for a query using SQL (Oracle specifically). Basically, I am looking for duplicate entries for certain columns and while I can find those what I also want is some kind of indicator to say what rows the duplicates are from.
Below is an example of what I am looking to do (looking for duplicates on Name, Zip, Phone). The rows with Name = aaa are all in the same group, bb are not, and c are.
Is there even a way to do this? I was thinking something with OVER (PARTITION BY ... but I can't think of a way to only increment for each group.
+----------+---------+-----------+------------+-----------+-----------+
| Name | Zip | Phone | Amount | Duplicate | Group |
+----------+---------+-----------+------------+-----------+-----------+
| aaa | 1234 | 5555555 | 500 | X | 1 |
| aaa | 1234 | 5555555 | 285 | X | 1 |
| bb | 545 | 6666666 | 358 | | 2 |
| bb | 686 | 7777777 | 898 | | 3 |
| aaa | 1234 | 5555555 | 550 | X | 1 |
| c | 5555 | 8888888 | 234 | X | 4 |
| c | 5555 | 8888888 | 999 | X | 4 |
| c | 5555 | 8888888 | 230 | X | 4 |
+----------+---------+-----------+------------+-----------+-----------+
