List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 17 1999 12:31pm
Subject:SQL question, part 2
View as plain text  
>>>>> "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
Thread
SQL question, part 2Harry Brueckner17 Mar
  • SQL question, part 2Michael Widenius17 Mar
  • Re: SQL question, part 2Paul Wolstenholme17 Mar
AW: SQL question, part 2Christian Stocker17 Mar