List:General Discussion« Previous MessageNext Message »
From:Harry Brueckner Date:March 17 1999 11:07am
Subject:SQL question, part 2
View as plain text  
Hi,
I posted a question about this problem last week already but I still could not
solve it.

I have a table like:

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

Now I try to run a query like:

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

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

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

What I expected to get is the row

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

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

Whats wrong there with my statement ?

Harry



 ===================================================================
  PGP fingerprint: B9 EE 83 0C 1F 48 54 50  9F A7 7A 00 6C 94 36 02
  PGP public key available by fingering harry@stripped

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