Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Question of the Day for 30 Nov 2005 Expand / Collapse
Author
Message
Posted Tuesday, November 29, 2005 4:05 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
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.
Post #240550
Posted Wednesday, November 30, 2005 6:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 30, 2005 6:29 AM
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

Post #240701
Posted Wednesday, November 30, 2005 6:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 30, 2005 6:29 AM
Points: 2, Visits: 1

Please Ignore my previous post.

Doh! Doh! Doh!

 

Post #240712
Posted Wednesday, November 30, 2005 7:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 23, 2007 6:53 AM
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.

 

 

 

Post #240742
Posted Wednesday, November 30, 2005 8:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 23, 2007 6:53 AM
Points: 107, Visits: 1
Opps. See it was too early in the morning. All "nonaggregated columns".
Post #240748
Posted Wednesday, November 30, 2005 8:02 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 7:19 AM
Points: 2,148, Visits: 284
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.



Post #240753
Posted Wednesday, November 30, 2005 10:05 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, January 17, 2014 11:05 AM
Points: 779, 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.
Post #240818
Posted Wednesday, November 30, 2005 11:26 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 9:53 AM
Points: 3,475, Visits: 584
I saw solutions that used Group By without aggregates.


Regards,
Yelena Varshal

Post #240846
Posted Thursday, December 1, 2005 7:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:05 AM
Points: 7,056, Visits: 7,279
All nonaggregated columns in the SELECT clause must also be listed in the GROUP BY clause

If we're nitpicking 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.

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 ;)



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

Anon.

Post #241111
Posted Monday, March 3, 2008 2:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 8:25 AM
Points: 159, 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.
Post #463432
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse