Различать по столбцу, используя заказ

Question

У меня есть вид БД.

На этом этапе он возвращает строки, которые могут иметь повторяющиеся записи в id колонка. Я хотел бы сделать следующее:

SELECT DISTINCT ON (id) USING DESCRIBED BELOW RULE -- of course it is not valid sql
  id
  type

type столбец может иметь 3 значения:

• x
• y
• z

Если есть строки с одинаковыми id но разные type, правило для DISTINCT как следует:

• выберите строку с типом x первый
• выберите строку с типом y если нет x
• выберите строку с типом z если нет x и нет y

Итак, если у меня есть 3 строки:

id    type
1     'y'
1     'x'
2     'z'

Ожидаемый результат будет

id    type
1     'x'
2     'z'

Есть ли способ добиться этого?

Answer 1

You may put the hierarchy in a CASE выражение в 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; 

демонстрация

Answer 2

Если ваши значения упорядочены по алфавиту в реальном (как в вашем примере), это будет очень просто - вам нужно только упорядочить значения:

SELECT DISTINCT ON (id)
    id,
    type
FROM mytable m
ORDER BY id, type

---

демо: дб<> скрипка

Если нет, то вам понадобится таблица, в которой хранятся ранжированные значения и их ранжированный порядок. Или вы имитируете это с помощью подзапроса, как я сделал:

SELECT DISTINCT ON (id)
    id,
    type
FROM mytable m
JOIN (VALUES (1, 'x'), (2, 'y'), (3, 'z')) AS r (rank_id,value)
ON r.value = m.type
ORDER BY id, r.rank_id

---

Если ваши данные типа действительно фиксированы, вы можете подумать о типе enum, который является упорядоченным типом по умолчанию. Запрос будет выглядеть так, как показано выше:

демо:db<> скрипка

Но обратите внимание, что изменение перечислений (добавление, удаление, перестановка значений) может быть очень сложным. Поэтому рекомендуется, только если тип действительно фиксированный. В противном случае возьмите отдельную таблицу с внешними ключами, которая возвращает вас ко второй части.