Simple Group By question - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Simple Group By question

Author

Topic

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-06-30 : 05:35:34

Before I post the queston, is it possible to enable HTML on this forum?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-30 : 05:45:07

What do you want to do?
If it's to keep the formating of code then just put
(square bracket)code(square bracket)
(square bracket)/code(square bracket)
round it.

And this should be in a site related forum rather than transact sql.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-30 : 05:47:03

refer to the Forum FAQ http://www.sqlteam.com/forums/faq.asp

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-06-30 : 06:11:37

The question is about T-SQL.

There's a big "HTML is OFF" written to the left of this message box. Many forums allow the option of having this turned on. I'd then be able to post tables without the columns being all over the place. The FAQ does not tell you how to switch on HTML

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-30 : 06:21:21

The original question was about the site not about sql.
You have now asked multiple question on the same thread.
Try to be friendly to the people who are trying to help.

as to your second question something like
select *
from tbl t1
where classification = 'Cereals'
and quantity = (select max(t2.quantity) from tbl t2 where t2.countrty = t1.country and t2.classification = t1.classification)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-06-30 : 06:57:00

quote:
Try to be friendly to the people who are trying to help.

The response was not meant to be construed as unfriendly. Here's some smilies...

(This is not a sarcastic response!)

Your answer works, but I can't get my head round it. Can you explain your thought process behind it, or the logical process... please.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-30 : 07:01:34

(select max(t2.quantity) from tbl t2 where t2.countrty = t1.country and t2.classification = t1.classification)

That's a correlated subquery - it contains a reference to a column in the outer query.
You can think of it executing for each row in th eouter query
so for each row it gets the max cereal quantity for that country.
The outer query only takes entries with that quantity.

so you end up with the cereal entries with the max quantity for each country.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-06-30 : 07:06:17

Beautiful. Thanks.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-30 : 07:12:41

A minor thought, but you might get better performance (and no warnings) if you hard-code the 2nd t1.classification too...

select *
from tbl t1
where classification = 'Cereals'
and quantity = (select max(t2.quantity) from tbl t2
where t2.countrty = t1.country and t2.classification = t1.classification 'Cereals')

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-07-05 : 04:47:27

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources