How to use DISTINCT ON (of PostgreSQL) in Firebird?

distinct, distinct-on, firebird, sql

Solution

PostgreSQL's `DISTINCT ON` takes the first row per stated group key considering the `ORDER BY` clause. In other DBMS (including later versions of Firebird), you'd use `ROW_NUMBER` for this. You number the rows per group key in the desired order and stay with those numbered #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;

In your example you have a tie (key_1 = 2 / key_2 = 0001 occurs twice) and the DBMS picks one of the rows arbitrarily. (You'd have to extend the sortkey both in `DISTINCT ON` and `ROW_NUMBER` to decide which to pick.) If you want two rows, i.e. showing all tied rows, you'd use `RANK` (or `DENSE_RANK`) instead of `ROW_NUMBER`, which is something `DISTINCT ON` is not capable of.

Problem

I have a TempTable with datas: ``` ------------------------------------ | 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 | ------------------------------------ ``` I want to get the following data: ``` ------------------------------------ | KEY_1 | KEY 2 | NAME | VALUE | ------------------------------------ | 1 | 0001 | NAME 2 | VALUE 1 | | 2 | 0001 | NAME 1 | VALUE 2 | ------------------------------------ ``` In PostgreSQL, I use a query with `DISTINCT ON`: ``` SELECT DISTINCT ON (KEY_1) KEY_1, KEY_2, NAME, VALUE FROM TempTable ORDER BY KEY_1, KEY_2 ``` In Firebird, how to get data as above datas?

Original source