Assign Ids to a paired group

Question

I have a table with some asset information that looks like this:

| ID |  PairID | AssetName   | PairAssetName | 
|----|---------|-------------|---------------|
|  1 | 3       | Case1       | Case3         |
|  4 | 2       | Case4       | Case2         |
|  3 | 1       | Case3       | Case1         |
|  2 | 4       | Case2       | Case4         |

As you can see the ID and the PairIDs are related one to another with data on the same table. I need to assign a group ID that allows me to identify them in an incremental way. Something that looks like this:

| ID |  PairID | AssetName   | PairAssetName| GroupID |
|----|---------|-------------|--------------|---------|
|  1 | 3       | Case1       | Case3        | Group1  |
|  4 | 2       | Case4       | Case2        | Group2  |
|  3 | 1       | Case3       | Case1        | Group1  |
|  2 | 4       | Case2       | Case4        | Group2  |

Which will allow me to identify pairs in an easier way, knowing that sometimes, some rows won't have a pair at all. Is there a way to do this in SQL? It doesn't need to say Group1 but with the number would be more than enough.

Answer 1

Assuming an integer is sufficient for the groupid, then one method is dense_rank():

select t.*,
       dense_rank() over (order by case when id < pairid then id else pairid end,
                                   case when id < pairid then pairid else id end
                         ) as groupid                                   
from t;