CODEWITHSUNDEEP
sqloracle-database
Surendra Singh
Surendra Singh

Reputation: 19

Updating column on comparing of two rows value from same table

I am trying to format my result into the following:

ID    pred  probability cost  
101   1     0.15        0.9  
101   2     0.85        0.1  
102   2     0.25        0.55  
102   1     0.75        0.44

The required format is to get result column on the basis of higher probability for e.g., for ID 101 the probability of pred value '2' is higher so result column will have '2' as value for ID '101' :

ID    result  pred  cost  
101   2       1     0.9  
101   2       2     0.1  
102   1       2     0.55  
102   1       1     0.44

I tried getting the max probability by grouping on basis of ID like: 

SELECT  
   ID,  
    MAX(probability) prob  
FROM  
    table  
GROUP BY  
    ID

and concatenating the other rows on basis of ID and probability like: 

with temp as (  
SELECT  
        ID,  
        MAX(probability) prob  
    FROM  
        table  
    GROUP BY  
        ID  
)  
select id,
base.ID, base.pred, base.cost
from base
where temp.ID = base.ID and base.probability = temp.prob

but not getting the expected output. Any suggestions?

Upvotes: 0

Views: 59

Answers (3)

Amir Kadyrov
Amir Kadyrov

Reputation: 1288

with s  (ID, pred, probability, cost) as (
select 101,   1,     0.15,        0.9  from dual union all
select 101,   2,     0.85,        0.1  from dual union all
select 102,   2,     0.25,        0.55 from dual union all
select 102,   1,     0.75,        0.44 from dual)
select
s.*,
nth_value(pred, 1) ignore nulls over (partition by id order by probability desc) nth
from s
order by id, probability;

        ID       PRED PROBABILITY       COST        NTH
---------- ---------- ----------- ---------- ----------
       101          1         .15         .9          2
       101          2         .85         .1          2
       102          2         .25        .55          1
       102          1         .75        .44          1

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

use window function

 with cte as

(
select 101 as id, 1 as pred, 0.15 as pro, 0.9 as cost from dual
union all
select 101,2,0.85,0.1 from dual
union all
select 102,2,0.25,0.55 from dual
union all
select 102,1,0.75,.44 from dual
)
, cte1 as
(
select t.*,  max(pro) over(partition by ID) as result
from cte t
) select cte1.id,cte.pred as result,cte1.pred,cte1.cost 
from cte1 left join cte on cte1.result=cte.pro
 order by cte1.ID,cte1.cost desc,cte1.pred

output

    ID  RESULT  PRED    COST
   101      2     1     .9
   101      2     2     .1
   102      1     2     .55
   102      1     1     .44

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

use last_value function

LAST_VALUE(pred) IGNORE NULLS
         OVER (PARTITION BY ID ORDER BY probability ROWS BETWEEN
           UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS result

Demo

Upvotes: 1

Related Questions