How to use DISTINCT ON (PostgreSQL) in Firebird?
I have a TempTable with data:
------------------------------------
| 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 receive 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 like above data?
source to share
PostgreSQL DISTINCT ON
takes the first row for the specified group key, given the sentence ORDER BY
. In other DBMSs (including later versions of Firebird) use ROW_NUMBER
. You specify the number of lines per group of keys in the order you want and are left 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 happens twice) and the DBMS picks one of the rows at random. (You will need to expand the sort on both in DISTINCT ON
and ROW_NUMBER
out to decide which to select.) If you want two rows, that is, showing all related rows, you must use RANK
(or DENSE_RANK
) instead of ROW_NUMBER
, something is DISTINCT ON
not capable of.
source to share
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 the 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 duplicates for key_2
. Alas ... getting just one row is tricky if you don't have a unique ID for each row.
source to share