Как использовать DISTINCT ON (из PostgreSQL) в Firebird?

Question

У меня есть TempTable с данными:

------------------------------------
| KEY_1 | KEY 2 | NAME   | VALUE   |
------------------------------------
|     1 |  0001 | NAME 2 | VALUE 1 |
|     1 |  0002 | NAME 1 | VALUE 3 |
|     1 |  0003 | NAME 3 | VALUE 2 |
|     2 |  0001 | NAME 1 | VALUE 2 |
|     2 |  0001 | NAME 2 | VALUE 1 |
------------------------------------

Я хочу получить следующие данные:

------------------------------------
| KEY_1 | KEY 2 | NAME   | VALUE   |
------------------------------------
|     1 |  0001 | NAME 2 | VALUE 1 |
|     2 |  0001 | NAME 1 | VALUE 2 |
------------------------------------

В PostgreSQL я использую запрос с DISTINCT ON:

SELECT DISTINCT ON (KEY_1) KEY_1, KEY_2, NAME, VALUE
FROM TempTable
ORDER BY KEY_1, KEY_2

В Firebird, как получить данные, как указано выше данных?

Answer 1

PostgreSQL, DISTINCT ON занимает первую строку в указанном групповом ключе с учетом ORDER BY пункт. В других СУБД (включая более поздние версии Firebird) вы бы использовали ROW_NUMBER за это. Вы нумеруете строки для каждого группового ключа в желаемом порядке и остаетесь с номерами #1.

select key_1, key_2, name, value
from
(
  select key_1, key_2, name, value,
    row_number() over (partition by key_1 order by key_2) as rn
  from temptable
) numbered
where rn = 1
order by key_1, key_2;

В вашем примере у вас есть связь (key_1 = 2 / key_2 = 0001 встречается дважды), и СУБД выбирает одну из строк произвольно. (Вы должны расширить ключ сортировки как в DISTINCT ON а также ROW_NUMBER решить, какой из них выбрать.) Если вы хотите, чтобы две строки, т.е. отображали все связанные строки, вы должны использовать RANK (или же DENSE_RANK) вместо ROW_NUMBERчто-то DISTINCT ON не способен на.

Answer 2

Firebird 3.0 поддерживает оконные функции, поэтому вы можете использовать:

select . . .
from (select t.*,
             row_number() over (partition by key_1 order by key_2) as seqnum
      from temptable t
     ) t
where seqnum = 1;

В более ранних версиях вы можете использовать несколько методов. Вот коррелированный подзапрос:

select t.*
from temptable t
where t.key_2 = (select max(t2.key_2)
                 from temptable t2
                 where t2.key_1 = t.key_1
                );

Примечание. Это по-прежнему будет возвращать повторяющиеся значения для key_1 из-за дубликатов для key_2, Увы,, получить только одну строку сложно, если у вас нет уникального идентификатора для каждой строки.