SQL plus min/max problem 3

[accessguy52]

Hi - I'm just starting on an Oracle 9i db and my supervisor has asked me to run a query that displays the least expensive product and most expensive product in our inventory. Easy right? Just use min/max function. But, I also need to have the product name too. And I can't display that as a group function. Anyone know how to get around this? Sounds like one of those simple classroom questions you get on a test. Jeez, Im embarrassed ! I'm tearing my hair out here! Any help appreciated.

Product Price
Example output : Clairol Shampoo 1.99
Mink Coat 4,000

Thanks to any and all who can help!

accessguy52

 

[ddrillich]

try -

select product_name
from products
where price = (select max(price) from products)

 

[SantaMufasa]

select name,SUGGESTED_WHLSL_PRICE from s_product
where SUGGESTED_WHLSL_PRICE in (select min(SUGGESTED_WHLSL_PRICE) from s_product)
union
select name,SUGGESTED_WHLSL_PRICE from s_product
where SUGGESTED_WHLSL_PRICE in (select max(SUGGESTED_WHLSL_PRICE) from s_product)
order by SUGGESTED_WHLSL_PRICE;

NAME SUGGESTED_WHLSL_PRICE
-------------------------------------------------- ---------------------
Major League Baseball 4.29
Grand Prix Bicycle 1669

2 rows selected.

Is this what you wanted?

Dave

 

[Turkbear]

Hi,
Or Try subqueries:

Select MaxProd.Prodname as "Top Cost",MaxProd.Prodcost as "Most Expensive Product",MinProd.Prodname as "Low Cost",MinProd.Prodcost as "Least Expensive Product" 
from
(select Prodname,Prodcost from products where prodcost
  = (select max(prodcost from products)) MaxProd,
(select Prodname,Prodcost from products where prodcost
  = (select min(prodcost from products)) MinProd;

Check the parens, I may have missed a pair, but it should give you the idea....

 

[accessguy52]

Thanks, all 3 of you, I'll give those a try and see what happens. (Hey, Turkbear, been a while since I chatted with you.)Will post my results later...

accessguy52

 

[accessguy52]

Thank you, SantaMafusa, for the union query. That seemed to satisfy my boss. Plus, it was very instructive for me. I'll keep it in mind for more things like that. You have my undying gratitude. And a big thanks to the rest of you, too.



accessguy52

 

[sem]

I'm not sure on whether Oracle engine is clever enough to eliminate extra s_product scans in its internals, but I think that query below performs only 2 scans as opposite to Dave's 4:

select name, SUGGESTED_WHLSL_PRICE
from s_product p,
(select min(SUGGESTED_WHLSL_PRICE) mn
, max(SUGGESTED_WHLSL_PRICE) mx from s_product) b
where p.SUGGESTED_WHLSL_PRICE = b.mn or p.SUGGESTED_WHLSL_PRICE = b.mx

Regards, Dima