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

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?

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.