This week's book giveaway is in the OCAJP forum.
We're giving away four copies of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) and have Khalid A Mughal & Rolf W Rasmussen on-line!
See this thread for details.
Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

bigger value in function MAX(x,y)

 
Fabiano Souza
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic