From: Michael Widenius
Date: March 17 1999 12:31pm
Subject: SQL question, part 2
List-Archive: http://lists.mysql.com/mysql/407
Message-Id: <14063.40988.582482.425791@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

>>>>> "Harry" == Harry Brueckner <brueckner@stripped> writes:

Harry> Hi,
Harry> I posted a question about this problem last week already but I still could not
Harry> solve it.

Harry> I have a table like:

Harry> | Shop | Article | Amount |
Harry> | A    |       1 |      3 |
Harry> | B    |       1 |     17 |
Harry> | C    |       1 |      9 |
Harry> | D    |       1 |     25 |
Harry> | A    |       2 |     14 |
Harry> | B    |       2 |     29 |
Harry> | G    |       2 |      1 |
Harry> | D    |       3 |    212 |
Harry> | E    |       3 |     17 |
Harry> | F    |       3 |     35 |

Harry> Now I try to run a query like:

Harry> SELECT Shop, Article, Amount, max(Amount) AS val FROM mytable WHERE Article = 1
Harry> GROUP BY Article;

Harry> The problem which I face is, that I want to find the row with the highest Amount
Harry> value but instead I get something like:

Harry> | Shop | Article | Amount | val |
Harry> |    A |       1 |      3 |  25 |

Harry> What I expected to get is the row

Harry> | Shop | Article | Amount | val |
Harry> | D    |       1 |     25 |  25 |

Harry> Where the maximum really is in. Instead MySQL takes the 1st match for the GROUP
Harry> BY statement and "adds" the max() value.

Harry> Whats wrong there with my statement ?

Harry> Harry

Hi!

The above problem is described in the MySQL manual, section
'Group by functions'.  (The problem is that your query is illegal
according to ANSI SQL and you are using a MySQL extension without
understanding the consequences)

Regards,
Monty