I have a DB view.
At this point it returns rows which can have duplicate entries in id
column.
I would like to do the following:
SELECT DISTINCT ON (id) USING DESCRIBED BELOW RULE -- of course it is not valid sql
id
type
type
column can have 3 values:
x
y
z
If there are rows with identical id
but different type
, the rule for DISTINCT
is as follows:
x
firsty
if no x
z
if no x
and no y
So if I have 3 rows:
id type
1 'y'
1 'x'
2 'z'
The expected result will be
id type
1 'x'
2 'z'
Is there a way to achieve this?
You may put the hierarchy in a CASE
expression in ORDER BY
SELECT DISTINCT ON (id) *
FROM ( VALUES (1, 'y'),
(1, 'x'),
(2, 'z') ) s(id, type)
ORDER BY id,
CASE type
WHEN 'x' THEN 1
WHEN 'y' THEN 2
WHEN 'z' THEN 3
END;