hi
what about :
first get the max amount with
> SELECT max(amount) AS val FROM mytable WHERE Article = 1
GROUB BY article;
then make a second select
> SELECT Shop, Article, Amount FROM mytable WHERE Article = 1 and Amount =
> $val
> GROUP BY Article;
>
or something like that (didn't test it, don't have access to mysql form my
office...) (you can't to this in simple SQL you need a programming language
like perl or php or so, to do that..)
no more ideas, sorry
chregu
---
Reply-To: chregu@stripped
everything else is futile...
> ----------
> Von: Harry Brueckner[SMTP:brueckner@stripped]
> Gesendet: Mittwoch, 17. März 1999 12:07
> An: MySQL Mailinglist
> Betreff: SQL question, part 2
>
> 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
>
>
>
>
