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 |
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