From: Paul Wolstenholme Date: March 17 1999 6:17pm Subject: Re: SQL question, part 2 List-Archive: http://lists.mysql.com/mysql/440 Message-Id: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII 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 > >