Combine pairs from two columns with dense_rank

sh4rkyy 0
Viewed 16

I have pairs of values that I'd like to group with common ID.

Sample data FIDDLE

create table sample_data(value_a integer, value_b integer);
insert into sample_data values
(100,200),
(400,500),
(400,600),
(800,900),
(800,1500),
(1000,800);

So far I tried this one, which works only when common value is value_a

select value_a as value, dense_rank() over(order by value_a) as group_id
from sample_data 
UNION 
select value_b as value, dense_rank() over(order by value_a) as group_id
from sample_data
order by 2,1

First two groups are fine, but I want last 3 rows from the table to be grouped together like this:

100, 1
200, 1
400, 2
500, 2
600, 2
800, 3
900, 3
1000, 3
1500, 3
postgresql
1 Answers

Ok, I've made it using some way around by this sh*tty query

with sd2 as (
select value_a,value_b, dense_rank() over(order by value_a) as group_id
from sample_data sd
            )
, pre_result as (
    select sd2.value_a, sd2.value_b, (select group_id from sd2 s2 where 
                                    sd2.value_b = s2.value_a 
                                    or sd2.value_a = s2.value_b 
                                    or sd2.value_a = s2.value_a 
                                    or sd2.value_b = s2.value_b
                                                order by 1 limit 1)
    from sd2 )

select group_id, value_a as id from pre_result
union 
select group_id, value_b as id from pre_result
order by 1,2
sh4rkyy0
Related