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

Question

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?

score 7 · Answer 1 · answered May 13 '17 at 18:58

Firebird 3.0 supports window functions, so you can use:

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

In earlier versions, you can use several methods. Here is a correlated subquery:

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

Note: This will still return duplicate values for key_1 because of the duplicates for key_2. Alas . . . getting just one row is tricky unless you have a unique identifier for each row.

"unless you have a unique identifier for each row" ...and you do. http://www.ibexpert.net/ibe/index.php?n=Doc.TheMysteryOfRDBDBKEY — Arioch 'The, May 15 '17 at 17:16

score 7 · Accepted Answer · answered May 13 '17 at 18:59

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.

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

2 Answers2