?
Solved

SQL Question

Posted on 2013-01-13
4
Medium Priority
?
265 Views
Last Modified: 2013-01-14
I need to Display the part id, description, and price for the highest and lowest priced parts in the  inventory.   When I run the query below it gives me all the rcords in the inventory table.  

How should modify it ?  Thanks

SELECT partid,description,
        MAX(price) AS highPrice,
        MIN(price) AS lowPrice
FROM INVENTORY
GROUP BY partid,description
0
Comment
Question by:ocdc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38773227
It is possible that you could have multiple stock items with the same low price and same high price.

Something like this might get you started.

select a.partid, a.description, x.minprice, b.partid, b.description, x.maxprice
from inventory a
join 
(select min(price) as minprice, max(price) as maxprice 
 from inventory) as x on a.price = x.minprice
join inventory b on b.price = x.maxprice

Open in new window


In the event that you have one unique minprice and one unique maxprice you'll get one simple row.

In the event that you have more than one of each, you're going to get effectively a cartesian join, showing you every permutation of the values of min and max.
0
 

Author Comment

by:ocdc
ID: 38773240
just did this: when I look at the INVENTORY.Price column in INVENTORY table,   there are two items one with low 2.50  the other one is high 80.00.  The query below gives me the mentioned columns.   Any feedback ?

SELECT i.Partid
      ,i.Description
      ,i.Price    
FROM INVENTORY i  
WHERE  i.Price IN
(SELECT MAX(Price)
 FROM INVENTORY)
GROUP BY i.PartID
        ,Description
        ,i.Price
UNION ALL
SELECT i.PartID
      ,Description
      ,i.Price
FROM INVENTORY i  
WHERE  i.Price IN
(SELECT MIN(Price)
FROM INVENTORY)
GROUP BY i.PartID,i.Description,i.Price
0
 

Author Comment

by:ocdc
ID: 38775035
I've requested that this question be deleted for the following reason:

no need anymore
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 2000 total points
ID: 38773366
That query will do the job and give you as many rows in your result set as there are Mins and Maxes (and will look prettier than mine).

Mine gave it all on one row (with duplicates), yours gives multiple rows in the result set - so whatever works.

Would probably change the IN to = though.

WHERE  i.Price =
(SELECT MIN(Price)
FROM INVENTORY)

Do the same for Max.

In is usually for when you don't know how many rows will be in your result set.  Since you're using Min and Max you'll have exactly one Min and exactly one Max - so use equals.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question