Re: MAX() oddity

Mark Legate writes:
> I may be off here however when running this query:
> How-To-Repeat:
> SELECT MAX(id), field2 FROM table WHERE field3 = 'x' GROUP BY field2
>
> This returns multiple results. In my mind it should only return 1
> result - the one with the MAX(id). If it wasn't intended to operate in
> this fasion it really should as there is no other way to select a single
> result like this without doing an ORDER BY id DESC LIMIT 1 or doing 2
> selects. Which, while all well and good aren't the most efficient ways
> to return a single result.
>
> JMJimmy

Hi!

The above is expected behaviour.

When any of aggregating queries are used, then you will get all
distinct values of field2, with the accompanying maxumum value of id
for each distinct value of field2.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13738@lists.mysql.com
To unsubscribe, e-mail