Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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"Received on Wed Apr 23 2003 - 20:00:47 CDT