Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!

bigger value in function MAX(x,y)

Fabiano Souza
Greenhorn
Posts: 11
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
Greenhorn
Posts: 14
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
Greenhorn
Posts: 11
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=?)

Fabiano Souza
Greenhorn
Posts: 11
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
Greenhorn
Posts: 14
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
Ranch Hand
Posts: 338
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:

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 ]