From: Harry Brueckner
Date: March 17 1999 11:07am
Subject: SQL question, part 2
List-Archive: http://lists.mysql.com/mysql/399
Message-Id: <199903171107.MAA05832@nero.respublica.de>
MIME-Version: 1.0
Content-Type: text/plain
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