Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


create table data_tab( dev text, int text) ;

insert into data_tab(dev, int) 
     values ('sw01', 'gi1')
          , ('sw01', 'gi2')
          , ('sw01', 'gi3')
          , ('sw01', 'gi4')
          , ('sw02', 'gi3')
          , ('sw02', 'gi4')
          , ('sw02', 'gi5')
          , ('sw03', 'gi3')
          , ('sw03', 'gi4')
          , ('sw03', 'gi6')
          , ('sw03', 'gi7');

CREATE TABLE



INSERT 0 11


with dev_array as 
     ( select array_agg(distinct dev) dev 
         from data_tab
     ) 
   , int_match as 
     (select int, array_agg(dev) dev
        from data_tab
       group by int
     )
select int, unnest(dev) dev
  from (select im.*
          from dev_array da
          join int_match im  
            on (im.dev = da.dev)
         order by im.int
         limit 1
       ) sq


int
dev


gi3
sw01

gi3
sw02

gi3
sw03



SELECT 3