db2-luw

DB2 Group by specific columns


ID_1 ID_2 ID_3 LANG
1 11 111 F_lang
1 11 111 null
2 22 222 null

Is it possible to build a query which returns only these two lines below. It should be grouped by the first three columns and should take only that line with value of column LANG which is preferably not null, if no value for LANG exists, it should take the line with language null.

ID_1 ID_2 ID_3 LANG
1 11 111 F_lang
2 22 222 null

Solution

  • Does this anwser the question ?

    with t1(id_1, id_2, id_3, lang) as (
      VALUES
      ('1', '11', '111', 'F_lang'),
      ('1', '11', '111', NULL),
      ('2', '22', '222', NULL),
      ('3', '33', '333', 'F_lang_3_1'),
      ('3', '33', '333', 'F_lang_3_2'),
      ('3', '33', '333', NULL)
    )
    select id_1, id_2, id_3, lang from t1 where lang is not null
    union all
    select id_1, id_2, id_3, null as lang from t1 group by id_1, id_2, id_3 having max(lang) is null
    order by id_1, id_2, id_3
    
    ID_1 ID_2 ID_3 LANG
    1 11 111 F_lang
    2 22 222 null
    3 33 333 F_lang_3_1
    3 33 333 F_lang_3_2

    fiddle