aspose file tools*
The moose likes Oracle/OAS and the fly likes bigger value in function MAX(x,y) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "bigger value in function MAX(x,y)" Watch "bigger value in function MAX(x,y)" New topic
Author

bigger value in function MAX(x,y)

Fabiano Souza
Greenhorn

Joined: Sep 26, 2008
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

Joined: Nov 04, 2008
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

Joined: Sep 26, 2008
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=?)



and thanks for replying!
Fabiano Souza
Greenhorn

Joined: Sep 26, 2008
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

Joined: Nov 04, 2008
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

Joined: Oct 06, 2007
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 ]
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: bigger value in function MAX(x,y)