CODEWITHSUNDEEP
sqloracle-database
Soon
Soon

Reputation: 501

Using data in other columns

My table is as below.

WITH mytable as (
    select 1 as serial, 'm1' as m, 10 as p, null as q from dual
    union
    select 2, 'm1', 12, null  from dual
    union
    select 3, 'm1', null, 5  from dual
    union
    select 4, 'm2', 20 , null from dual
    union
    select 5, 'm1', null, 6  from dual
    union  
    select 6, 'm2', null, 4   from dual
    ) select * from mytable;

My goal is to get P divide by Q. The problem is the nominator and denominator are not exist in the same row. Moreover, for each nominator and denominator, I want to use the rows with high value in the serial column partitioned by M. The expected result is

M   Calculated_value
---  ----------------
m1    2               <--- 12 at the serial 2  divided by 6 in the rownumber 5
m2    5               <--- 20    "             4        "    5                  6

Upvotes: 0

Views: 50

Answers (3)

Soon
Soon

Reputation: 501

with mytable as (
select 1 as serial, 'm1' as m, 10 as p, null as q from dual
union
select 2, 'm1', 12, null  from dual
union
select 3, 'm1', null, 5  from dual
union
select 4, 'm2', 20 , null from dual
union
select 5, 'm1', null, 6  from dual
union
select 6, 'm2', null, 4   from dual
)
select -- serial, m, p,q,p_found,q_found,
m,p_found/q_found
from
(
select serial,m,p,q
,row_number() over ( partition by m order by serial) as row_num
,first_value(p ignore nulls) over (partition by m order by serial desc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) p_found
,first_value(q ignore nulls) over (partition by m order by serial desc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) q_found
from mytable
) 
where row_num=1
;

Upvotes: 0

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

Looks like you need this:

select 
  m 
 ,max(p)keep(dense_rank first order by decode(p,null,null,serial) desc nulls last) max_p
 ,max(q)keep(dense_rank first order by decode(q,null,null,serial) desc nulls last) max_q
 ,max(p)keep(dense_rank first order by decode(p,null,null,serial) desc nulls last) 
  /
  max(q)keep(dense_rank first order by decode(q,null,null,serial) desc nulls last) 
  as result
from mytable
group by m;

M       MAX_P      MAX_Q     RESULT
-- ---------- ---------- ----------
m1         12          6          2
m2         20          4          5

Full example:

with mytable(serial,m,p,q) as (
select 1, 'm1', 10, null from dual
union
select 2, 'm1', 12, null from dual
union
select 3, 'm1', null, 5  from dual
union
select 4, 'm2', 20 , null from dual
union
select 5, 'm1', null, 6  from dual
union  
select 6, 'm2', null, 4   from dual
) 
select 
  m 
 ,max(p)keep(dense_rank first order by decode(p,null,null,serial) desc nulls last) max_p
 ,max(q)keep(dense_rank first order by decode(q,null,null,serial) desc nulls last) max_q
 ,max(p)keep(dense_rank first order by decode(p,null,null,serial) desc nulls last) 
  /
  max(q)keep(dense_rank first order by decode(q,null,null,serial) desc nulls last) 
  as result
from mytable
group by m;

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142713

Here's one option; code you might need begins at line #14:

SQL> with mytable as (
  2  select 1 as serial, 'm1' as m, 10 as p, null as q from dual
  3  union
  4  select 2, 'm1', 12, null  from dual
  5  union
  6  select 3, 'm1', null, 5  from dual
  7  union
  8  select 4, 'm2', 20 , null from dual
  9  union
 10  select 5, 'm1', null, 6  from dual
 11  union
 12  select 6, 'm2', null, 4   from dual
 13  ),
 14  temp as
 15    (select m,
 16         first_value(p ignore nulls) over (partition by m order by p desc) p,
 17         first_value(q ignore nulls) over (partition by m order by q desc) q
 18     from mytable
 19    )
 20  select m,
 21    max(p) / max(q) calculated_value
 22  from temp
 23  group by m;

M  CALCULATED_VALUE
-- ----------------
m1                2
m2                5

SQL>

Upvotes: 2

Related Questions