比较postgreSQL表中的行，并保留列中字符串最长的行

Question

我在PostgreSQL 11.0中有以下表格

id      col1                             col2           code
3876    dexamethasone                   dexamethasone   A01AC | C05AA | D07AB | S01BA
3948    dexamethasone sodium phosphate  dexamethasone   A01AC | C05AA 
187834  dexamethasone sodium succinate  dexamethasone   H02AB | S01BA
352241  dexamethasone acetate           dexamethasone   D07AB | H02AB | S01BA
971608  dexamethasone phosphate         dexamethasone   H02AB
1010    insulin plus                    insulin         H02ABA | H02ABC
10101   paracet                         insul           H02A
10101   paracetamol                     insul           H02A

如果各行的col2值相同，而id值不同，我希望保留代码字符串最长的行(或id的最小值)。其余行保持不变。

所需的输出为：

id      col1                             col2           code
3876    dexamethasone                   dexamethasone   A01AC | C05AA | D07AB | S01BA
1010    insulin plus                    insulin         H02ABA | H02ABC
10101   paracet                         insul           H02A
10101   paracetamol                     insul           H02A

对于这个问题，我没有可以尝试的起点。非常感谢您的帮助。

Answer 1

db-fiddle here

with dup_ids as (
  select id
    from codes
   group by id
  having count(*) > 1
)
select distinct on (c.col2||case when d.id is null then '' else c.col1 end) c.*
  from codes c
  left join dup_ids d on d.id = c.id
 order by (c.col2||case when d.id is null then '' else c.col1 end), length(c.code) desc, c.id;

Answer 2

如果我没看错的话，您可以使用distinct on和row_number()

select distinct on (
    col2, 
    row_number() over(partition by col2, id order by id)
) t.*
from mytable t
order by
    col2, 
    row_number() over(partition by col2, id order by id),
    length(code) desc, 
    id
;