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
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 (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

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

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

Group: General Forum Members
Points: 2523 Visits: 344
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1207 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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

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


Regards,
Yelena Varshal

David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9564 Visits: 9753
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-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 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.
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3091 Visits: 2766
I think A is correct, I dont knw why correct answer is D while it is not fully correct Cool

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
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