 |
AppletTalk.com
Java discussions newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Roedy Green
Guest
|
 Posted: Fri Jun 18, 2004 2:01 am Post subject: simple max function? |
 |
|
This is not legit SQL. I wondered if you could do what I was
attempting here in one statement though.
conn.prepareStatement( "UPDATE vendors SET
highestVendorConfirm=MAX(?,highestVendorConfirm),
vendorTimestamp=MAX(?,vendorTimestamp) WHERE vendorId=?" );
I am using max not in the column-examining sense, but just a function
to pick the bigger of two numbers.
--
Canadian Mind Products, Roedy Green.
Coaching, problem solving, economical contract programming.
See http://mindprod.com/jgloss/jgloss.html for The Java Glossary.
|
|
| Back to top |
|
 |
Roedy Green
Guest
|
| Posted: Fri Jun 18, 2004 4:44 am Post subject: Re: simple max function? |
|
|
On Fri, 18 Jun 2004 02:01:32 GMT, Roedy Green
<look-on (AT) mindprod (DOT) com.invalid> wrote or quoted :
| Quote: | conn.prepareStatement( "UPDATE vendors SET
highestVendorConfirm=MAX(?,highestVendorConfirm),
vendorTimestamp=MAX(?,vendorTimestamp) WHERE vendorId=?" );
|
I have found references to the LEAST and GREATEST function, but they
don't seem to be mentioned in the MYSQL docs, though LEAST in
mentioned on the one MySQL website.
--
Canadian Mind Products, Roedy Green.
Coaching, problem solving, economical contract programming.
See http://mindprod.com/jgloss/jgloss.html for The Java Glossary.
|
|
| Back to top |
|
|
Raquel
Guest
|
| Posted: Fri Jun 18, 2004 5:42 am Post subject: Re: simple max function? |
|
|
Guess I am not understanding your question well...though it might be vendor
dependent, I am not sure which vendor would NOT provide MAX functionality
on non-columns. I work on DB2 and the syntax for MAX is:
MAX(expression)
where expression might be a column-name or a 'constant'. I checked
MAX(2,3,4) here on DB2 and sure enough, it returned 4.
Regards,
Raquel.
|
|
| Back to top |
|
|
Roedy Green
Guest
|
| Posted: Fri Jun 18, 2004 6:18 am Post subject: Re: simple max function? |
|
|
On Fri, 18 Jun 2004 01:42:13 -0400, "Raquel" <raquel (AT) nospam (DOT) com> wrote
or quoted :
| Quote: |
where expression might be a column-name or a 'constant'. I checked
MAX(2,3,4) here on DB2 and sure enough, it returned 4.
|
This is what drives me nuts about SQL. Every vendor picks and chooses
the names of function and their semantics. You can't write generic
SQL.
It turns out that's the way MySQL used to work, then they reserved MAX
solely for the max of column function.
Now they have these two undocumented but working functions LEAST and
GREATEST.
--
Canadian Mind Products, Roedy Green.
Coaching, problem solving, economical contract programming.
See http://mindprod.com/jgloss/jgloss.html for The Java Glossary.
|
|
| Back to top |
|
|
Lee Fesperman
Guest
|
| Posted: Fri Jun 18, 2004 6:52 am Post subject: Re: simple max function? |
|
|
Roedy Green wrote:
| Quote: |
This is not legit SQL. I wondered if you could do what I was
attempting here in one statement though.
conn.prepareStatement( "UPDATE vendors SET
highestVendorConfirm=MAX(?,highestVendorConfirm),
vendorTimestamp=MAX(?,vendorTimestamp) WHERE vendorId=?" );
I am using max not in the column-examining sense, but just a function
to pick the bigger of two numbers.
|
Use a CASE expression.
--
Lee Fesperman, FirstSQL, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)
|
|
| Back to top |
|
|
Mark Matthews
Guest
|
| Posted: Sat Jun 19, 2004 1:19 am Post subject: Re: simple max function? |
|
|
Roedy Green wrote:
| Quote: | On Fri, 18 Jun 2004 01:42:13 -0400, "Raquel" <raquel (AT) nospam (DOT) com> wrote
or quoted :
where expression might be a column-name or a 'constant'. I checked
MAX(2,3,4) here on DB2 and sure enough, it returned 4.
This is what drives me nuts about SQL. Every vendor picks and chooses
the names of function and their semantics. You can't write generic
SQL.
It turns out that's the way MySQL used to work, then they reserved MAX
solely for the max of column function.
|
Hm...as far as I can remember, 'max' has _always_ been an aggregate
function in MySQL, but I could be wrong.
| Quote: |
Now they have these two undocumented but working functions LEAST and
GREATEST.
|
They're documented, maybe you were just looking in the wrong place?
http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html
-Mark
--
Mr. Mark Matthews
MySQL AB, Software Development Manager, J2EE and Windows Platforms
Office: +1 708 332 0507
www.mysql.com
MySQL Guide to Lower TCO
http://www.mysql.com/it-resources/white-papers/tco.php
|
|
| Back to top |
|
|
Roedy Green
Guest
|
| Posted: Sat Jun 19, 2004 1:32 am Post subject: Re: simple max function? |
|
|
On Fri, 18 Jun 2004 20:19:27 -0500, Mark Matthews <mark (AT) mysql (DOT) com>
wrote or quoted :
| Quote: | Now they have these two undocumented but working functions LEAST and
GREATEST.
They're documented, maybe you were just looking in the wrong place?
|
see if you can find something in manual.html
Where else could I look?
--
Canadian Mind Products, Roedy Green.
Coaching, problem solving, economical contract programming.
See http://mindprod.com/jgloss/jgloss.html for The Java Glossary.
|
|
| Back to top |
|
|
Dieter Bender
Guest
|
| Posted: Sat Jun 19, 2004 9:11 am Post subject: Re: simple max function? |
|
|
Raquel wrote:
| Quote: | Guess I am not understanding your question well...though it might be
vendor dependent, I am not sure which vendor would NOT provide MAX
functionality on non-columns. I work on DB2 and the syntax for MAX is:
MAX(expression)
where expression might be a column-name or a 'constant'. I checked
MAX(2,3,4) here on DB2 and sure enough, it returned 4.
|
in my opinion thats no contradiction to sql standard. (2, 3, 4) is treated
as a table with one column.
Dieter Bender
|
|
| Back to top |
|
|
Mark Matthews
Guest
|
| Posted: Mon Jun 21, 2004 3:34 pm Post subject: Re: simple max function? |
|
|
Roedy Green wrote:
| Quote: | On Fri, 18 Jun 2004 20:19:27 -0500, Mark Matthews
wrote or quoted :
Now they have these two undocumented but working functions LEAST and
GREATEST.
They're documented, maybe you were just looking in the wrong place?
see if you can find something in manual.html
Where else could I look?
|
Roedy,
That link I posted was the same manual that ships with your MySQL
server. Therefore you can either use the link I posted, or use 'find' in
your browser on manual.html.
Make sureusing a manual.html that shipped with your server version, the
manual does get updated, the one that ships with a given server version
is the one that is 'current' for that server.
You can also always use the one on our website, features that are
available starting from a specific version are usually documented that
way (i.e. 'from 4.0.18 and up...).
Regards,
-Mark
--
Mr. Mark Matthews
MySQL AB, Software Development Manager, J2EE and Windows Platforms
Office: +1 708 332 0507
www.mysql.com
MySQL Guide to Lower TCO
http://www.mysql.com/it-resources/white-papers/tco.php
|
|
| Back to top |
|
|
Roedy Green
Guest
|
| Posted: Mon Jun 21, 2004 4:12 pm Post subject: Re: simple max function? |
|
|
On Mon, 21 Jun 2004 10:34:04 -0500, Mark Matthews <mark (AT) mysql (DOT) com>
wrote or quoted :
| Quote: | That link I posted was the same manual that ships with your MySQL
server. Therefore you can either use the link I posted, or use 'find' in
your browser on manual.html.
|
I have the manual for version 4.0.20a. I am using 4.0.20a. I am
constrained in my choice of version by the team I am working with.
--
Canadian Mind Products, Roedy Green.
Coaching, problem solving, economical contract programming.
See http://mindprod.com/jgloss/jgloss.html for The Java Glossary.
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
