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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: Administrators
Points: 18581 Visits: 222
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 (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 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 (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

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

Group: General Forum Members
Points: 2722 Visits: 357
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
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5657 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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

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


Regards,
Yelena Varshal

David Burrows
David Burrows
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31792 Visits: 10706
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
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 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