From: Christian Stocker
Date: March 17 1999 12:02pm
Subject: AW: SQL question, part 2
List-Archive: http://lists.mysql.com/mysql/402
Message-Id: <B1C3A3767714D1118BD7006097B4FFCF20DFAF@mail.ebp.ch>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="---- =_NextPart_001_01BE706D.FE75E100"

------ =_NextPart_001_01BE706D.FE75E100
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

hi

what about :
first get the max amount with

> SELECT max(amount) AS val FROM mytable WHERE Article =3D 1=20
GROUB BY article;

then make a second select

> SELECT Shop, Article, Amount FROM mytable WHERE Article =3D 1 and =
Amount =3D
> $val
> GROUP BY Article;
>=20
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=E4rz 1999 12:07
> An: 	MySQL Mailinglist
> Betreff: 	SQL question, part 2
>=20
> Hi,
> I posted a question about this problem last week already but I still =
could
> not
> solve it.
>=20
> I have a table like:
>=20
> | 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 |
>=20
> Now I try to run a query like:
>=20
> SELECT Shop, Article, Amount, max(Amount) AS val FROM mytable WHERE
> Article =3D 1
> GROUP BY Article;
>=20
> The problem which I face is, that I want to find the row with the =
highest
> Amount
> value but instead I get something like:
>=20
> | Shop | Article | Amount | val |
> |    A |       1 |      3 |  25 |
>=20
> What I expected to get is the row
>=20
> | Shop | Article | Amount | val |
> | D    |       1 |     25 |  25 |
>=20
> Where the maximum really is in. Instead MySQL takes the 1st match for =
the
> GROUP
> BY statement and "adds" the max() value.
>=20
> Whats wrong there with my statement ?
>=20
> Harry
>=20
>=20
>=20
>=20

------ =_NextPart_001_01BE706D.FE75E100--