Geting max value at runtime

[rawatds]

Hi,
My requirement is that i have to get the MAX or Greatest of the three values from three tables and replace each value with the Hightest value.

Example :

select
age2 = c.age - b.age,
age3 = a.age - b.age,
age1 = a.age
from a , b , c
where a.name = b.name
and a.deptid = c.deptid

Now my requirement is that to check which age is greatest ( heightest ) among age1 , age2 , age3 and then assign each age i.e. age1 , age2 and age3 with that heightest age.


I have to do it in SQL only . No pl/sql please


Regards

Rawat

 

[engineer2100]

When you say


Now my requirement is that to check which age is greatest ( heightest ) among age1 , age2 , age3 and then assign each age i.e. age1 , age2 and age3 with that heightest age.

do you mean to update the data in the tables or store the values into some local variables?

If it is later, try this out

select greatest(age1, age2,age3),greatest(age1, age2,age3),greatest(age1, age2,age3)
into nage1, nage2, nage3
from
(
select 
(c.age - b.age) age2,
(a.age - b.age) age3,
a.age age1 
from a , b , c
where a.name = b.name 
and a.deptid = c.deptid)

where nage1, nage2, nage3 are local variables


As a sample run this

select greatest(a1,a2,a3) from 
(select 2 a2, 4 a3, 1a1 from dual);

HTH
Engi