equivalent of distinct On in Oracle-CodePudding

How to translate the following query to Oracle SQL, as Oracle doesn't support distinct on()?

select distinct on (t.transaction_id) t.transaction_id as transactionId , 
t.transaction_status as transactionStatus ,
c.customer_id as customerId , 
c.customer_name as customerName,

CodePudding user response：

You can use ANY_VALUE with group by for this:

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/any_value.html

Example: https://dbfiddle.uk/WUxvjv5J

with t (a,b,c) as (
select 1,10,1 from dual union all
select 1,10,2 from dual union all
select 1,10,3 from dual union all
select 1,20,4 from dual union all
select 1,20,5 from dual union all
select 1,30,7 from dual
)
select a,b,any_value(c)
from t
group by a,b;

CodePudding user response：

Yes, Oracle has a full set of windowing functions you can use for this. The simplest is ROW_NUMBER:

SELECT *
  FROM (SELECT x.col1,
               x.col2,
               x.col3,
               ROW_NUMBER() OVER (PARTITION BY x.col1 ORDER BY x.col2 DESC) seq
          FROM table x) 
 WHERE seq = 1

for each distinct col1, it will number the highest col2 value as seq=1, the next highest as seq=2, etc... so you can filter on 1 to get the desired row. You can used as complex ORDER BY logic as you need to pick the row you want. The key thing is that the ORDER BY goes inside the ROW_NUMBER OVER clause along with the distinct (PARTITION BY) definition, not outside in the main query block.