databasepostgresqldistinctdatabase-view

Distinct on column using custom order


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:

If there are rows with identical id but different type, the rule for DISTINCT is as follows:

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?


Solution

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

    Demo