Solved

SQL Question

Posted on 2013-01-13
4
262 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 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Express connecting form remote error 26 7 55
How can I get this column in my query? 2 56
My Query is not giving correct result. Please help 5 55
Parse this column 6 35
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…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

733 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