0

I'm in a beginner SQL class using Microsoft SQL Studio. This is a very stupid question, I apologize but I can't figure it out. I'd like to know the proper syntax in order to do this, preferably in multiple ways because I know in SQL there isn't only one way to do things.

We haven't done any joins/subqueries so I'm not sure I should be using those, again this is a very basic intro class...

Where I need to do: Show INVENTORY table: Price, Part, Description (columns) Instructions: Display the Part, Description, and Price for the highest and lowest Priced parts in INVENTORY.

Here is what I have:

  SELECT TOP(1) Price, Part, Description FROM INVENTORY
  ORDER BY Price DESC;

This shows the highest price but how do I get the lowest price as well? Using AND doesn't work

I also did 

 SELECT MIN(price), 
 SELET MAX(price), 
 part, 
 description 
 from inventory

But it tells me that Description and Part aren't in aggregate functions.

I tried doing GROUP BY after WHERE as in GROUP BY part but then it says that description isnt in an aggregate function. So then I do GROUP BY part, description and then every result shows up when I only want the min and max.

So it looks like:

 SELECT MIN(Price), 
 MAX(Price), 
 Part, 
 Description 
 FROM Inventory
 GROUP BY Part, Description

But then it displays every result. I just want the highest and lowest results and using TOP with AND doesnt work.

Can someone help me with my syntax? What am I doing wrong? I'd appreciate two ways to show this, one using TOP and one using MIN/MAX if possible. I've been researching over an hour and even posted this to 4chan to ask for help.

Steven Ackley
  • 593
  • 7
  • 31
pseudosql
  • 1
  • 1
  • I'm confused why my question was marked as a duplicate? I've never posted here before. – pseudosql Jan 12 '16 at 21:05
  • The duplicate closure means that this question was asked and answered by someone else before (it doesn't have to have been you). Look at the top of the screen, above your post; there is a link to that question. – Paul Richter Jan 12 '16 at 21:11
  • Oh I see. Thank you but that person's question is very advanced and I'm not really seeing the similarities... – pseudosql Jan 12 '16 at 21:27
  • Check out the accepted answer (the one with the green checkmark). It, however, uses joins and subqueries, which is probably the simplest approach. Is the point of this assignment to get you to figure those concepts out, perhaps? Beyond that, have you learned about ´union´? You might be able to achieve what you want by unioning the query you have with the ´order by´ twice; one ´DESC´, the other with ´ASC´. – Paul Richter Jan 12 '16 at 21:46
  • Hi again. I don't understand the other person's post that was linked at the top. Subqueries and Joins we have not learned, this is only week 1. We will learn Subqueries and Joins after midterms. Sorry I tried to do a linebreak (enter) and it posted, I didn't mean to. I just started reading up on UNIONS but I'm having issues with it (see my other comment below) thank you. – pseudosql Jan 12 '16 at 21:49
  • SELECT TOP(1) Price, Part, Description FROM INVENTORY <-- this query UNION ALL SELECT TOP(1) Price, Part, Description FROM INVENTORY <-- is the same as this query.. so the same row is being displayed and 'unions' do not repeat. My solution is to do an ORDER BY Price DESC and ORDER BY Price ASC after each statement but I can't do that because it's invalid syntax. I can only have ONE ORDER BY statement, and that's at the end of both queries. So I'm not sure how to go about this, but yes I've eben doing a lot of research. – pseudosql Jan 12 '16 at 21:51
  • You forgot the order bys in your union query. One query in the union will do order by DESC, the other one will do order by ASC. – Paul Richter Jan 12 '16 at 22:16

0 Answers0