Updating Group Number based on swap records in postgresql-CodePudding

I just have a two column says like below

Ref     Comp
 A       B
 B       A

I have the data like this like swapping. Now i just need to provided the same group number for both the records like mentioned below. I our case both the records are same so i need to provide same number for both the records in seperate column. Please provide any solution for this.

GROUP      REF       COMP
  1         A         B
  1         B         A

CodePudding user response：

You can use the Window Function dense_rank ... in the over(...) use just the order clause: (see demo)

select dense_rank() over( order by least(ref,comp), greatest(ref,comp) ) as "Group" 
     , ref 
     , comp
 from <your_table>  
order by "Group", least(ref,comp);

For demo, I added a couple additional data rows. I seldom trust test result set with only 1 basic item. In this case "Group".