Using MIN with GROUP BY

From: D <doraleet_at_yahoo.com>
Date: 23 Apr 2003 15:15:19 -0700
Message-ID: <e9e0def5.0304231415.229f6dca@posting.google.com>

I am having a problem gaining specific results from the SQL statement I've wrote. Without paying too much attention to the data, please help.

My SQL statement:
SELECT can.name, cook.flavor, cake.icing, dnuts.cake,

       MIN (dnuts.max_avail)
  FROM candy can,

       cookies cook,
       cake cake,
       doughnuts dnuts

 WHERE can.count = 95
   AND dnuts.count = 1
   AND can.count = cook.count
   GROUP BY can.name,

          cook.flavor,
          cake.icing,
          dnuts.cake

My results:
row   name           flavor          icing      dnuts    min
1     snickers       chocolate       chocolate  cake      21
2     mars           chocolate       chocolate  nocake    25
3     butterfinger   peanut butter   vanilla    cake      24

I thought this would only give me back row 1. But it seems as though it is basing my MIN off of the groups instead of the rows.

Does anyone know how I can write it without using a HAVING clause so that I only get row 1?

Thanks
DT Received on Wed Apr 23 2003 - 17:15:19 CDT