CODEWITHSUNDEEP
sqlpostgresql
user7384730
user7384730

Reputation: 11

Select pair where value of other column is equal

How do I select a pair (number, number) where tabid is equal for two numbers from the following table (i.e: number 7 and 11 have the same tabid):

   tabid  |  number
 ---------+--------
     1    |   6
     1    |   6
     2    |   7
     3    |   8
     4    |   8
     5    |   10
     5    |   11
     6    |   12
     6    |   11
     5    |   6
     4    |   7
     3    |   8
     2    |   11

The result of this should be:

   number |  number
 ---------+--------
     7    |   11
     7    |   8
     10   |   11
     11   |   12
     6    |   10
     6    |   11

Upvotes: 0

Views: 570

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use array_agg to concatenate the tabid's into an array. Thereafter self join this cte to check if one array is an overlap of the other using the array operator &&.

with concatenated as (
select array_agg(tabid) as arr_tab, num
from t
group by num
)
select c1.num,c2.num
from concatenated c1
join concatenated c2 on c1.num < c2.num
where c2.arr_tab && c1.arr_tab
order by 1,2

Sample Demo

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

Is this what you're looking for:

select
  t1.number, t2.number
from t t1, t t2
where t1.tabid = t2.tabid
and t1.number < t2.number;

produces:

    NUMBER     NUMBER
---------- ----------
         6         10 
         6         11 
         7          8 
         7         11 
        10         11 
        11         12

Upvotes: 2

Related Questions