Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Question

Posted on 2013-01-13
4
Medium Priority
?
266 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 23

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 23

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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

604 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