Re: Using MIN with GROUP BY

D wrote:
>
> 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

Can I assume the question is "show me the name,flavor,icing,etc for which the max avail is the smallest?". If so the query would look more like:

select name,flavor,icing,...
from ...
where max_avail = (
  select min(max_avail)
  from doughnuts)

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"