Home » SQL & PL/SQL » SQL & PL/SQL » Getting MAx value runtime
Getting MAx value runtime [message #246089] Tue, 19 June 2007 21:15 Go to next message
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
Re: Getting MAx value runtime [message #246103 is a reply to message #246089] Tue, 19 June 2007 23:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Getting MAx value runtime [message #246272 is a reply to message #246103] Wed, 20 June 2007 08:22 Go to previous messageGo to next message
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
Re: Getting MAx value runtime [message #246275 is a reply to message #246089] Wed, 20 June 2007 08:28 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Dharmender,

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

Regards
Michel
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:
  


Current Time: Thu Dec 08 22:09:08 CST 2016

Total time taken to generate the page: 0.09437 seconds