Very basic SQL problem. Can't figure out TOP or MIN/MAX?

  • (2 Pages)
  • +
  • 1
  • 2

23 Replies - 1573 Views - Last Post: 14 January 2016 - 04:29 AM

#1 pseudosql   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 12-January 16

Very basic SQL problem. Can't figure out TOP or MIN/MAX?

Posted 12 January 2016 - 01:55 PM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: Very basic SQL problem. Can't figure out TOP or MIN/MAX?

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15506
  • View blog
  • Posts: 62,083
  • Joined: 12-June 08

Re: Very basic SQL problem. Can't figure out TOP or MIN/MAX?

Posted 12 January 2016 - 02:08 PM

MSSQL or MYSQL?

.. Moved to MSSQL.
Was This Post Helpful? 0
  • +
  • -

#3 pseudosql   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 12-January 16

Re: Very basic SQL problem. Can't figure out TOP or MIN/MAX?

Posted 12 January 2016 - 02:10 PM

View Postmodi123_1, on 12 January 2016 - 02:08 PM, said:

MSSQL or MYSQL?


I'm not sure what the difference is but the language is SQL and we are using a program called Microsoft SQL Server Managemtent Studio
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15506
  • View blog
  • Posts: 62,083
  • Joined: 12-June 08

Re: Very basic SQL problem. Can't figure out TOP or MIN/MAX?

Posted 12 January 2016 - 02:12 PM

Perhaps poke at the msdn docs..

TOP just grabs the first X rows. Nothing more, nothing less.

Quote

Limits the rows returned in a query result set to a specified number of rows or percentage of rows i

https://msdn.microso...y/ms189463.aspx

Quote

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.


Again, peep at what min/max are doing..

https://msdn.microso...y/ms179916.aspx
https://msdn.microso...y/ms187751.aspx


Aggregate functions - typically a group by because you want the row with the min most value of a column over a group of similar rows, right?
Was This Post Helpful? 0
  • +
  • -

#5 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,218
  • Joined: 07-April 08

Re: Very basic SQL problem. Can't figure out TOP or MIN/MAX?

Posted 12 January 2016 - 02:13 PM

So we won't do your work for you, but we'll give you help. Try looking at the UNION Clause: https://msdn.microso.../ms180026.aspx. It will basically take two queries and join their result sets into one.

SELECT
    TOP 1 FOO
FROM BAR
ORDER BY ID DESC;

UNION ALL

SELECT
    TOP 1 FOO
FROM BAR
ORDER BY ID ASC;


Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15506
  • View blog
  • Posts: 62,083
  • Joined: 12-June 08

Re: Very basic SQL problem. Can't figure out TOP or MIN/MAX?

Posted 12 January 2016 - 02:14 PM

...
--------
Mysql is ..

Quote

MySQL is the world's most popular open source database.

http://www.mysql.com/about/

... which is different than MSSQL.
Was This Post Helpful? 0
  • +
  • -

#7 pseudosql   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 12-January 16

Re: Very basic SQL problem. Can't figure out TOP or MIN/MAX?

Posted 12 January 2016 - 02:25 PM

View Postmodi123_1, on 12 January 2016 - 02:12 PM, said:

Perhaps poke at the msdn docs..

TOP just grabs the first X rows. Nothing more, nothing less.

Quote

Limits the rows returned in a query result set to a specified number of rows or percentage of rows i

https://msdn.microso...y/ms189463.aspx

Quote

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.


Again, peep at what min/max are doing..

https://msdn.microso...y/ms179916.aspx
https://msdn.microso...y/ms187751.aspx


Aggregate functions - typically a group by because you want the row with the min most value of a column over a group of similar rows, right?

Hi, thank you.
TOP worked because I sorted it by DESC, but is there a way at the same time I can use TOP again and sort it by ASC, that way I get the highest and lowest value?

But I think the professor wants us to use MIN/MAX. I read those pages although they were very advanced (I have never programmed in any language before). I'm still confused by the aggregate functions part. I learned that you can use DISTINCT after a SELECT to get rid of duplicate values but there aren't any duplicate values. So I'm still stuck with it showing every single result as opposed to ONLY the min/max. Am I supposed to be using subqueries or joins?

View Postrgfirefly24, on 12 January 2016 - 02:13 PM, said:

So we won't do your work for you, but we'll give you help. Try looking at the UNION Clause: https://msdn.microso.../ms180026.aspx. It will basically take two queries and join their result sets into one.

SELECT
    TOP 1 FOO
FROM BAR
ORDER BY ID DESC;

UNION ALL

SELECT
    TOP 1 FOO
FROM BAR
ORDER BY ID ASC;



Thank you. I wasn't asking to do it for me, I want to understand how to do this. I'm a beginner with computers and this is my first class. I haven't heard of UNION before but when I tried to use it (in the same way the article did) I received a "Incorrect syntax near the keyword 'UNION'". UNION in the program is also red underlined, which I assume is because it's not a proper command?
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6833
  • View blog
  • Posts: 28,345
  • Joined: 12-December 12

Re: Very basic SQL problem. Can't figure out TOP or MIN/MAX?

Posted 12 January 2016 - 02:31 PM

There shouldn't be a semi-colon before the word UNION, it terminates the statement.

There should only be one ORDER BY clause as well.
Was This Post Helpful? 0
  • +
  • -

#9 pseudosql   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 12-January 16

Re: Very basic SQL problem. Can't figure out TOP or MIN/MAX?

Posted 12 January 2016 - 02:32 PM

View Postandrewsw, on 12 January 2016 - 02:27 PM, said:

There shouldn't be a semi-colon before the word UNION, it terminates the statement.

Thank you for the syntax advice. Although that didn't make the statement work, I still got incorrect syntax. Apparently I can't have the ORDER BY before the union. But now when I have the two top statements, it only outputs 1 result (and regardless of if I put ORDER BY at the second statement as ASC or DESC they show the same output) instead of 2 results.
Was This Post Helpful? 0
  • +
  • -

#10 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6833
  • View blog
  • Posts: 28,345
  • Joined: 12-December 12

Re: Very basic SQL problem. Can't figure out TOP or MIN/MAX?

Posted 12 January 2016 - 02:32 PM

UNION (Transact-SQL)
Was This Post Helpful? 0
  • +
  • -

#11 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15506
  • View blog
  • Posts: 62,083
  • Joined: 12-June 08

Re: Very basic SQL problem. Can't figure out TOP or MIN/MAX?

Posted 12 January 2016 - 02:39 PM

Quote

Apparently I can't have the ORDER BY before the union.

Well, no. Think about it. Union is mushing two queries together.. the it wouldn't make sense to have a union on one query before you mush it with the other.. the union goes at the end and applies to the post mushed set.
Was This Post Helpful? 0
  • +
  • -

#12 pseudosql   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 12-January 16

Re: Very basic SQL problem. Can't figure out TOP or MIN/MAX?

Posted 12 January 2016 - 02:43 PM

View Postmodi123_1, on 12 January 2016 - 02:39 PM, said:

Quote

Apparently I can't have the ORDER BY before the union.

Well, no. Think about it. Union is mushing two queries together.. the it wouldn't make sense to have a union on one query before you mush it with the other.. the union goes at the end and applies to the post mushed set.

I realized that after thinking about it, thank you.

But even as I have the syntax correct (and that Microsoft page can't help me with this issue)

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

It only returns ONE value as opposed to two.

Not only that, but it returns the same result even if I make the ORDER BY Price ASC:
SELECT TOP(1) Price, Part, Description FROM INVENTORY
UNION ALL
SELECT TOP(1) Price, Part, Description FROM INVENTORY ORDER BY Price ASC;

I did some research on subqueries but I can't make that work either, which is fine because we won't be learning sub-queries for a very long time. This is week 1.

SELECT TOP(1) * FROM (SELECT Price, PartID, Description FROM INVENTORY ORDER BY Price DESC)
UNION
SELECT TOP(1) * FROM (SELECT Price, PartID, Description FROM INVENTORY ORDER BY Price ASC)

^I know that is all wrong and I probably shouldn't be even attempting subquering.
Was This Post Helpful? 0
  • +
  • -

#13 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15506
  • View blog
  • Posts: 62,083
  • Joined: 12-June 08

Re: Very basic SQL problem. Can't figure out TOP or MIN/MAX?

Posted 12 January 2016 - 02:45 PM

Again.. think about it.
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.

ORDER BY Price DESC;


Was This Post Helpful? 0
  • +
  • -

#14 pseudosql   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 12-January 16

Re: Very basic SQL problem. Can't figure out TOP or MIN/MAX?

Posted 12 January 2016 - 02:48 PM

View Postmodi123_1, on 12 January 2016 - 02:14 PM, said:

...
--------
Mysql is ..

Quote

MySQL is the world's most popular open source database.

http://www.mysql.com/about/

... which is different than MSSQL.


Sorry I had no idea. I had never heard of SQL before this class.

View Postmodi123_1, on 12 January 2016 - 02:45 PM, said:

Again.. think about it.
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.

ORDER BY Price DESC;


Wow thank you so much for this explanation. It makes a lot of sense to me. So the reason that won't work is because I'm displaying the same one statement. So my solution is to include the ORDER BY DESC/ASC in each one but I can't do that. So i'm not sure what to do. Is using TOP the solution for this or should I go back to trying MIN/MAX and figuring out the HAVING/AGGREGATE functions?

Thanks.
Was This Post Helpful? 0
  • +
  • -

#15 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15506
  • View blog
  • Posts: 62,083
  • Joined: 12-June 08

Re: Very basic SQL problem. Can't figure out TOP or MIN/MAX?

Posted 12 January 2016 - 02:57 PM

You do not need to quote the post above yours. Use the 'reply' button

Quote

. So my solution is to include the ORDER BY DESC/ASC in each one

May the good Lord preserve me.. NO!


crea te table #foo (lVal int, sVal varchar(10))

ins ert into #foo(lval, sval)
values(1, 'one')

ins ert into #foo(lval, sval)
values(2, 'two')

ins ert into #foo(lval, sval)
values(3, 'three')


select lval
, sval
from #foo
--lval        sval
------------- ----------
--1           one
--2           two
--3           three

select top(1)  lval
, sval
from #foo
--lval        sval
------------- ----------
--1           one
-- (default order is by the first column.. so that's why that row is returned)

select min(lval) as min_lval
from #foo
-- min_lval
-------------
--1


select lval
, sval
from #foo
where lVal = 1

UNION

select lval
, sval
from #foo
where lVal = 3

--lval        sval
------------- ----------
--1           one
--3           three


select min(lval)
from #foo

union 

select max(lval)
from #foo

-------------
--1
--3

dr op tab le #foo 

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2