SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question of the Day for 30 Nov 2005


Question of the Day for 30 Nov 2005

Author
Message
Site Owners
Site Owners
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: Administrators
Points: 12621 Visits: 16
Comments posted to this topic are about the Question of the Day for 30 Nov 2005 posted at http://www.sqlservercentral.com/testcenter/qod.asp?QuestionID=651.
ravicabral
ravicabral
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1

It seems unlikely that everyone is wrong.

Even unlikelier that I am right! but -

SELECT COL1,COL2,COL3 FROM TABLE1 GROUP BY COL1

works!

However,

SELECT COL2,COL3 FROM TABLE1 GROUP BY COL1

does NOT work.

So doesn't that mean that "A" is, in fact true?

Rav


ravicabral
ravicabral
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1

Please Ignore my previous post.

Doh! Doh! Doh!


Tom Brackney
Tom Brackney
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 1

Actually A is not fully true. For instance,

You can say

SELECT col1, col2, max(col3) from TableA GROUP BY col1, col2

Therefore col3 is not technically in the group by clause.

I could be wrong. Its early and I have not had my coffee yet. But I think the above is true.


Tom Brackney
Tom Brackney
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 1
Opps. See it was too early in the morning. All "nonaggregated columns".
emiddlebrooks
emiddlebrooks
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2563 Visits: 348
Even though I got it correct I don't think it is completely correct.

select col1, col2, "extra" = 'X' from table1 group by col1, col2


would work even though the column "extra" is a non-aggregate and is not in the group by.



Aaron Ingold
Aaron Ingold
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2611 Visits: 222
That answer's not 100% accurate.

All nonaggregated columns in the SELECT clause must also be listed in the GROUP BY clause; assuming that there is at least one aggregated column in the SELECT or HAVING clauses.

You don't need to specify a GROUP BY clause if there are no aggregates in your SELECT or HAVING clauses.

But that's just being nitpicky.
Yelena Varshal
Yelena Varshal
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8818 Visits: 600
I saw solutions that used Group By without aggregates.


Regards,
Yelena Varshal

David Burrows
David Burrows
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16465 Visits: 10109
All nonaggregated columns in the SELECT clause must also be listed in the GROUP BY clause

If we're nitpicking Rolleyes then....

Even though I got it correct I don't think it is completely correct.


"extra" = 'X' is an expression not a column in the query and therefore is not bound by the above rule. Laugh

I saw solutions that used Group By without aggregates


Yep

SELECT col1 FROM [table] GROUP BY col1

yields the same result as

SELECT DISTINCT col1 FROM [table]

However GROUP BY includes an implicit COUNT(*) if not explicitly defined and therefore technically all GROUP BY's have an aggregate Wink


Far away is close at hand in the images of elsewhere.

Anon.


Jeremy Giaco
Jeremy Giaco
SSC Veteran
SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)

Group: General Forum Members
Points: 213 Visits: 122
I got this correct, but only because i had to assume that the "correct" answer wouldnt be totally correct.

Select 1 as "Col1", Col2, Col3
From Table
Group By Col2, Col3


...will work, and I would consider 1 a non-aggregate, or is that an incorrect statement.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search