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
Solved

SQL Question

Posted on 2013-01-13
4
261 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
  • 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 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

856 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