simple max function?

Thread view:

Roedy Green - 18 Jun 2004 03:01 GMT

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.

Signature

Canadian Mind Products, Roedy Green.
Coaching, problem solving, economical contract programming.
See http://mindprod.com/jgloss/jgloss.html for The Java Glossary.

Reply to this Message

Roedy Green - 18 Jun 2004 05:44 GMT

>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.

Signature

Canadian Mind Products, Roedy Green.
Coaching, problem solving, economical contract programming.
See http://mindprod.com/jgloss/jgloss.html for The Java Glossary.

Reply to this Message

Raquel - 18 Jun 2004 06:42 GMT

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.

Reply to this Message

Roedy Green - 18 Jun 2004 07:18 GMT

>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.

Signature

Canadian Mind Products, Roedy Green.
Coaching, problem solving, economical contract programming.
See http://mindprod.com/jgloss/jgloss.html for The Java Glossary.

Reply to this Message

Mark Matthews - 19 Jun 2004 02:19 GMT

>>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.
[quoted text clipped - 5 lines]
> 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.

> 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

Signature

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

Reply to this Message

Roedy Green - 19 Jun 2004 02:32 GMT

>> 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?

Signature

Canadian Mind Products, Roedy Green.
Coaching, problem solving, economical contract programming.
See http://mindprod.com/jgloss/jgloss.html for The Java Glossary.

Reply to this Message

Mark Matthews - 21 Jun 2004 16:34 GMT

>>> Now they have these two undocumented but working functions LEAST and
>>> GREATEST.
[quoted text clipped - 4 lines]
>
> 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

Signature

Reply to this Message

Roedy Green - 21 Jun 2004 17:12 GMT

>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.

Signature

Canadian Mind Products, Roedy Green.
Coaching, problem solving, economical contract programming.
See http://mindprod.com/jgloss/jgloss.html for The Java Glossary.

Reply to this Message

Dieter Bender - 19 Jun 2004 10:11 GMT

> Guess I am not understanding your question well...though it might be
> vendor dependent, I am not sure which vendor would NOT provide MAX
[quoted text clipped - 4 lines]
> 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

> Regards,
> Raquel.

Reply to this Message

Lee Fesperman - 18 Jun 2004 07:52 GMT

> This is not legit SQL. I wondered if you could do what I was
> attempting here in one statement though.
[quoted text clipped - 5 lines]
> 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.

Signature

Lee Fesperman, FirstSQL, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

Reply to this Message

simple max function?

Free Magazines