OraFAQ Forum: SQL & PL/SQL » Getting MAx value runtime

Home » SQL & PL/SQL » SQL & PL/SQL » Getting MAx value runtime

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Getting MAx value runtime [message #246089]

Tue, 19 June 2007 21:15

rawat_me
Messages: 45
Registered: September 2005

Member

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

Report message to a moderator

Re: Getting MAx value runtime [message #246103 is a reply to message #246089]

Tue, 19 June 2007 23:20

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

There is no difficulty to get the greatest value: just use GREATEST function.

You can't assign values to age2, age3 as they are calculated values and not stored columns or do you want to modify the underlying "age" columns so they fit the requirement?

Regards
Michel

Report message to a moderator

Re: Getting MAx value runtime [message #246272 is a reply to message #246103]

Wed, 20 June 2007 08:22

rawat_me
Messages: 45
Registered: September 2005

Member

Hi Michel,
I have done it By nested Case statement :

select
Case
when ( c.age - b.age ) > ( a.age - b.age )
then
case
when ( c.age - b.age ) > a.age
then ( c.age - b.age )
else
a.age
end
else
when ( a.age - b.age ) > a.age
then
case
when ( a.age - b.age ) > a.age
then
( a.age - b.age )
else
a.age
end
end age3,
Case
when ( c.age - b.age ) > ( a.age - b.age )
then
case
when ( c.age - b.age ) > a.age
then ( c.age - b.age )
else
a.age
end
else
when ( a.age - b.age ) > a.age
then
case
when ( a.age - b.age ) > a.age
then
( a.age - b.age )
else
a.age
end
end age2,
Case
when ( c.age - b.age ) > ( a.age - b.age )
then
case
when ( c.age - b.age ) > a.age
then ( c.age - b.age )
else
a.age
end
else
when ( a.age - b.age ) > a.age
then
case
when ( a.age - b.age ) > a.age
then
( a.age - b.age )
else
a.age
end
end age1
from a , b , c
where a.name = b.name
and a.deptid = c.deptid

It worked.
Thanks for ur suggestion.

Rawat

Report message to a moderator

Re: Getting MAx value runtime [message #246275 is a reply to message #246089]

Wed, 20 June 2007 08:28

MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA

Senior Member

You could try :

select 
greatest( (c.age - b.age),
   (a.age - b.age) ,
   (a.age)) as greatest_age
from a , b , c
where a.name = b.name 
and a.deptid = c.deptid

Not sure if that meets all your requirements though.

[Updated on: Wed, 20 June 2007 08:28]

Report message to a moderator

Re: Getting MAx value runtime [message #246279 is a reply to message #246272]

Wed, 20 June 2007 08:39

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Dharmender,

Don't you see the GREATEST function in the documentation?

Regards
Michel

Report message to a moderator

Previous Topic:	How to rollback a transaction which got commited
Next Topic:	i am so lazy, i cannot come up with a good title

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 07 05:32:12 CST 2024