From: Christian Mack
Date: March 12 1999 7:13pm
Subject: Re: SQL question
List-Archive: http://lists.mysql.com/mysql/143
Message-Id: <36E96775.2B82079D@compal.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Harry Brueckner wrote:
> 
> Hello,
> 
> I have problems getting the proper SQL statement for the following problem.
> 
> I have this table:
> 
> | 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 want to select each article just once, where it has the amount is the
> maximum.
> The result for my example should be:
> 
> | Shop | Article | Amount |
> | D    |       1 |     25 |
> | B    |       2 |     29 |
> | D    |       3 |    212 |
> 
> How can I select just these rows ?
> 
> Harry
> 
> PS: Does anyone know a good SQL book which helps to get those little harder
>     SQL questions answered. Something like a "SQL Cookbook" or something ?

Hi Harry

Try:
SELECT Shop, Article, Amount FROM table HAVING Amount = MAX( Amount )

Books:
'SQL for Dummies' is good to get an first insight.

Tschau
Christian