Блог

Каждая запись в моей таблице имеет случайный выбор этих строк, поэтому она выглядит так

 record   fruit
A        pear
A        apple_guava
A        pear_guava
A        mango_guava
B        mango
C        apple_guava
C        pear_guava
 

Я хочу получить наибольшее значение «fruit» для каждой записи, за исключением того, что я хочу, чтобы оно было отсортировано ниже, если оно содержит строку guava. Это результат, который я хотел бы получить, и я использую Snowflake SQL

вывод:

 record   fruit
A        pear
B        mango
C        apple_guava
 

В настоящее время я пытаюсь сделать это, присваивая каждому возможному значению fruit число с регистром, когда statemnet , используя max в этом списке и преобразуя результат обратно в строку. В моем случае это не сработает, потому что имена фруктов изменятся, поэтому мне не нужен жестко закодированный список

это то, что я пробовал

 select
   record,
   left(max(case when fruit ilike '%guava%' then z || fruit else fruit end), 1) as fruit
from table
group by 1 
 

Желаемые результаты для Snowflake (как помеченные):

• array_agg() можно заказать
• для порядка мы можем отсортировать сначала по guava существующим, а затем по фруктам.
• [0] получает первый элемент array_agg() .

 with data as (
    select split(value, '        ')[0]::string record, split(value, '        ')[1]::string fruit
    from table(split_to_table(
'A        pear
A        apple_guava
A        pear_guava
A        mango_guava
B        mango
C        apple_guava
C        pear_guava'
    , 'n'))
)           

select record
  , (array_agg(distinct fruit) 
     within group (
         order by contains(fruit, 'guava'), fruit
     ))[0]::string max_fruit
from data
group by record
 

Исходный вопрос был помечен как Postgres, так что это решение Postgres.

Предполагая, что у вас есть некоторый порядок — давайте использовать алфавитный для этого примера — тогда вы можете использовать distinct on :

 select distinct on (record) r.*
from t
order by record,
         (fruit not like '%guava%') desc,  -- non-guava first
         fruit desc;
 

Со Snowflake (или любой другой базой данных) вы можете использовать row_number() :

 select t.*
from (select t.*,
             row_number() over (partition by record.
                                order by (fruit not like '%guava%') desc, fruit desc
                               ) as seqnum
      from t
     ) t
where seqnum = 1;
 

Вот скрипка db<>.