List:General Discussion« Previous MessageNext Message »
From:Paul Wolstenholme Date:March 17 1999 6:17pm
Subject:Re: SQL question, part 2
View as plain text  

On Wed, 17 Mar 1999, Harry Brueckner wrote:

> 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;

You might want to get rid of group and/or add an order clause.  I'm not
sure why you would want to columns with the same value though.   

> 
> 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
> 
> 
> ---------------------------------------------------------------------
> To request this thread, e-mail mysql-thread399@stripped
> To unsubscribe, e-mail the address shown in the
> List-Unsubscribe header of this message.
> For additional commands, e-mail: mysql-help@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