homework help?! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

homework help?!

Author

Topic

touandcim
Starting Member

4 Posts

Posted - 2010-02-09 : 01:29:24

Hey guys, I'm brand spankin new to the forum. I am completely stumped on my homework. I'm new to Sql and am trying to code a Select statement that will return one vendor per city/state from a table (Vendors). It is supposed to have a nested select statement. I've tried it several ways but cannot get it to return just one vendor per city. The most logical way I can think of doing it is this, which doesn't accomplish my objective of using a nested select statement OR returning the right values.

Select Distinct VendorCity, VendorState, VendorName
From Vendors
Order By VendorState

Any suggestions would help! Thanks in advance!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-09 : 01:30:43

Hint : GROUP BY

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 05:26:45

whats the version of sql server you're using?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-09 : 05:36:06

quote:
Originally posted by visakh16

whats the version of sql server you're using?

Play nice viaskh16!

touandcim -- as it's homework I'm guessing that you'll want to stick to the most 'vanilla' sql possible and not get bogged down into specific methods for different versions of the database engine.

For a start your tutor has to understand your code!

Probably best to think of it like this:

There are multiple vendors per city.

For each city what criteria will determine the correct vendor to choose? I'm guessing you don't just want to choose a RANDOM vendor per city? (that would be pretty meaningless).

Once you have that condition -- the vendor per city with the highest turnover / earliest name in the alphabet / whatever the condition is then go look at khtan's post and look up the documentation on GROUP BY.

You mentioned that you think the answer is supposed to have a nested select statement. You can do this that way but to be frank -- it's not the best way by a country mile.

If you need to use a nested select statement though you will either want a DERIVED TABLE (the better way) or a SUB QUERY (the bad way)

Have a look at GROUP BY FIRST.

Feel free to post your attempts / table structure / data. You probably won't get a direct solution as you've been honest enough to say its for homework but you'll definitely get a lot of help.

best of luck.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 05:39:07

quote:
Originally posted by Transact Charlie

quote:
Originally posted by visakh16

whats the version of sql server you're using?

Play nice viaskh16!

Dont think I'm gonna spoon feed him with answer. I was just asking him version so that I could give him any alternate hints

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-09 : 05:49:04

quote:
Originally posted by visakh16

quote:
Originally posted by Transact Charlie

quote:
Originally posted by visakh16

whats the version of sql server you're using?

Play nice viaskh16!

Dont think I'm gonna spoon feed him with answer. I was just asking him version so that I could give him any alternate hints

I was more thinking that somehow we'd end up with a CROSS APPLY / WINDOWING FUNCTION / CTE answer that works great but which fails because the tutor doesn't understand it / too advanced for the course.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 05:54:58

quote:
Originally posted by Transact Charlie

quote:
Originally posted by visakh16

quote:
Originally posted by Transact Charlie

quote:
Originally posted by visakh16

whats the version of sql server you're using?

Play nice viaskh16!

Dont think I'm gonna spoon feed him with answer. I was just asking him version so that I could give him any alternate hints

I was more thinking that somehow we'd end up with a CROSS APPLY / WINDOWING FUNCTION / CTE answer that works great but which fails because the tutor doesn't understand it / too advanced for the course.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Oh I see

I think I'm becoming too much predictable these days with my approach

touandcim
Starting Member

4 Posts

Posted - 2010-02-09 : 13:59:58

Thanks a bunch for the input so far =)

So, I decided to try it NOT using a subquery. The reason I wanted to use a subquery was because the problem was from the chapter on subqueries. I still cannot get it to work, though. I don't understand why my query won't return distinct values when I am selecting more than one column. Here is the syntax:

Select Distinct VendorCity, VendorState, VendorName
From Vendors
Group By VendorCity, VendorState, VendorName
Having VendorName = MAX(VendorName)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 14:05:42

quote:
Originally posted by touandcim

Thanks a bunch for the input so far =)

So, I decided to try it NOT using a subquery. The reason I wanted to use a subquery was because the problem was from the chapter on subqueries. I still cannot get it to work, though. I don't understand why my query won't return distinct values when I am selecting more than one column. Here is the syntax:

Select Distinct VendorCity, VendorState, VendorName
From Vendors
Group By VendorCity, VendorState, VendorName
Having VendorName = MAX(VendorName)

DISTINCT will make sure only combination of values is distinct not any of individual column value.
so you need some kind of join which will cause all others except the one within each GROUP. Hint is that you need to apply some kind of aggregation along with GROUP ing

MrQuizzles
Starting Member

20 Posts

Posted - 2010-02-09 : 15:16:06

I've figured out a way to do it without using a GROUP BY clause, instead using a sub-query. It's not THAT horrible (it's essentially joining to an aggregate result table and then cutting duplicates with distinct), but it really is a lot easier to simply use a GROUP BY clause.

Since this is all coming from one table, you don't even need to do a join from what I can tell.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 00:14:07

quote:
Originally posted by MrQuizzles

I've figured out a way to do it without using a GROUP BY clause, instead using a sub-query. It's not THAT horrible (it's essentially joining to an aggregate result table and then cutting duplicates with distinct), but it really is a lot easier to simply use a GROUP BY clause.

Since this is all coming from one table, you don't even need to do a join from what I can tell.

yup no need of join if you're using subquery approach or using windowing functions

------------------------------------------------------------------------------------------------------
SQL Server MVP

touandcim
Starting Member

4 Posts

Posted - 2010-02-11 : 19:31:22

Hey thanks guys. Big thanks to Visakh and Charlie!! figured it out. thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 01:11:28

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-12 : 01:50:30

<<
I think I'm becoming too much predictable these days with my approach
>>

Custom Title

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 01:57:48

quote:
Originally posted by madhivanan

<<
I think I'm becoming too much predictable these days with my approach
>>

Custom Title

Madhivanan

Failing to plan is Planning to fail

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MrQuizzles
Starting Member

20 Posts

Posted - 2010-02-12 : 14:39:24

quote:
Originally posted by visakh16

quote:
Originally posted by MrQuizzles

I've figured out a way to do it without using a GROUP BY clause, instead using a sub-query. It's not THAT horrible (it's essentially joining to an aggregate result table and then cutting duplicates with distinct), but it really is a lot easier to simply use a GROUP BY clause.

Since this is all coming from one table, you don't even need to do a join from what I can tell.

yup no need of join if you're using subquery approach or using windowing functions

Well, in this case, the very simple

SELECT VendorCity, VendorState, MAX(VendorName) FROM Vendors
GROUP BY VendorCity, VendorState

would work, would it not? You'd only need a join if you wanted to display more information from the displayed vendors. Then again, I guess it's good to keep things scalable.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-13 : 09:58:39

It should work provided he doesnt have to display any other individual info.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-02-13 : 12:22:22

You said you wanted to use a subquery.... How about using a subquery to get the city names with DISTINCT and then a SELECT TOP and a WHERE IN (Subquery)? I'm trying not to give away what I am suggesting, but I'm not sure I have been clear.

Edit: Sorry... MAX not TOP, of course! Also, the subquery is not needed at all, but might be good for learning purposes?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources