bigger value in function MAX(x,y)

[Fabiano Souza]

hello everyone..

does someone know a way to get the bigger value between 2 values in a select statement?

example:

SELECT MAX(X, Y) FROM ...

[Dany Mendez]

I could not understand well your question, but if you are looking to determine the max of two numbers, not in a table you have several choices, two of them are:
1) Create a strored function MX and
select MX(x,y) from dual
2)
select MAX(C) from (select 1 as C from dual
union all
select 2 as C from dual
)
Assuming that you are using oracle.

[Fabiano Souza]

well,, sorry for confusion..

I was wondering if I could do something like this:

SELECT MAX (SELECT xc.price, (SELECT c.price FROM CPrice c WHERE c.id=?) FROM XPrice xc WHERE xc.id=?)



and thanks for replying!

[Fabiano Souza]

just found what I needed

GREATEST..

SELECT GREATEST (xc.price, (SELECT c.price FROM CPrice c WHERE c.id=?)) FROM XCPrice xc WHERE xc.id=2262


Thanks again!!

[Dany Mendez]

Hello,

Still, if can do it like this:



SELECT MAX C from
(SELECT xc.price as C FROM XPrice xc WHERE xc.id=?)
union all
(SELECT c.price as C FROM CPrice c WHERE c.id=? )

[Paul Campbell]

Originally posted by Fabiano Souza:
just found what I needed

GREATEST..

SELECT GREATEST (xc.price, (SELECT c.price FROM CPrice c WHERE c.id=?)) FROM XCPrice xc WHERE xc.id=2262


Thanks again!!

You shouldn't put your query inside the function and should join the table and use it on the result... otherwise you will be peforming a query within the function for every row returned.

It should look more like this:

 
SELECT GREATEST (xc.price, c.price) AS hi_price
from XCPrice xc
   , CPrice c
WHERE c.id = xc.id
  and c.id=?;
 

Originally posted by Dany Mendez:
Hello,

Still, if can do it like this:



SELECT MAX C from
(SELECT xc.price as C FROM XPrice xc WHERE xc.id=?)
union all
(SELECT c.price as C FROM CPrice c WHERE c.id=? )

No, that would only give you the maximum value for C within your inline view... it would not tell you the highest price on each row within your table. In otherwords it would return a single value for your union, regardless of the number of rows queried.

Also... you should have parentheses around "C" in your example (MAX(C)).
[ December 10, 2008: Message edited by: Paul Campbell ]