Question 185882 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to link the pairs of values from 2 columns

I have a table [tt] with 2 columns which has a set of values that can repeat.
Columns are named [a] and [b]:

[a] [b]
1   2
1   3
1   4
2   1
2   3
3   2
4   1
4   5
5   4
5   6

I know how to query only the pairs:

SELECT t1.A, t1.B
    FROM tt AS t1
INNER JOIN tt AS t2
    ON t1.A = t2.B AND t1.B = t2.A

But how can I link the pairs together with adding a new column [pair_id] f.e. like this:

[a] [b] [pair_id]
1   2   1
2   1   1
1   4   2
4   1   2
2   3   3
3   2   3
4   5   4
5   4   4
Tags:

2

Answers


  1. 0

    Try the below query to link the pairs adding a new column [pair_id],

    SELECT t1.A, t1.B, ROW_NUMBER() OVER (ORDER BY t1.A, t1.B) AS [pair_id]
FROM tt AS t1
INNER JOIN tt AS t2
    ON t1.A = t2.B AND t1.B = t2.A
    Login or Signup to reply.
  2. 0

    Try the dense_rank function ordered by least and greatest functions.

    select a, b,
  dense_rank() over (order by least(a, b), greatest(a, b)) pair_id
from tbl

    Demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search