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 ««123»»

Group by Expand / Collapse
Author
Message
Posted Monday, September 6, 2010 3:12 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
Hugo Kornelis (9/6/2010)
tommyh (9/6/2010)
Hugo Kornelis (9/6/2010)
Good question!
In addition to your explanation, here is WHY an alias can be used in the ORDER BY, but not in the GORUP BY:


Because Microsoft havent implemented it.

No, because Microsoft has chosen not to go against the standard.

I think we see "implemented" a bit differently. I see it as something not being in the software (regardless of reason). And as for their reason. Well maybe it was because they didnt want to go against the standard, maybe not. Microsoft aint that know to obay standards.
Post #980968
Posted Monday, September 6, 2010 3:30 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, August 18, 2014 4:04 AM
Points: 176, Visits: 567
I got the answer right, after a long series of wrongs.

Hugo Kornelis (9/6/2010)
Good question!

1. FROM clause. Build an intermediate set as a copy of the single table in the FROM clause, or as the result of joining the multiple tables. Or supply a single-row result set with no columns if no FROM is specified.


Whoa!! Hugo, I always thought this behavior was from Visual Foxpro, to copy the entire table first before selecting the data.

I always thought in SQL, the data was Selected first before Where and Group, etc. Is
Post #980973
Posted Monday, September 6, 2010 3:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:05 PM
Points: 6,043, Visits: 8,323
Open Minded (9/6/2010)
I got the answer right, after a long series of wrongs.

Hugo Kornelis (9/6/2010)
Good question!

1. FROM clause. Build an intermediate set as a copy of the single table in the FROM clause, or as the result of joining the multiple tables. Or supply a single-row result set with no columns if no FROM is specified.


Whoa!! Hugo, I always thought this behavior was from Visual Foxpro, to copy the entire table first before selecting the data.

I always thought in SQL, the data was Selected first before Where and Group, etc. Is

Just remember, the description I gave is for the logical processing order. Actual processing order is always different - the optimizer picks the "best" plan to get the results. Making a competely redundant copy of a table will never be included in that plan!



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #980976
Posted Monday, September 6, 2010 4:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 13,253, Visits: 11,033
tommyh (9/6/2010)
da-zero (9/6/2010)

And who is we?


Hrmm having a hard time deciding if that is supposed to be sarcastic or not. But anyway.
I used "we" to represent everyone using SQL. Honestly dont know how else i could have written that.


Whoops. My bad.
I didn't see you were talking about table aliases instead of column aliases. So I thought you were referring to some database (other than SQL Server) where column aliases in the group by are possible.

Guess I'm not fully awake yet...




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #980991
Posted Monday, September 6, 2010 4:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 25, 2011 4:09 AM
Points: 1, Visits: 4
I parsed the query before running it.. and it gave me "Command(s) completed successfully.". Not sure why.. ?
Post #981015
Posted Monday, September 6, 2010 4:52 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
amresh_jhingan (9/6/2010)
I parsed the query before running it.. and it gave me "Command(s) completed successfully.". Not sure why.. ?


Syntax check only. And the syntax is okay. But that doesnt mean that it will run.

You can for instance parse say "select something from nothing" without having a table called "nothing".
Post #981021
Posted Monday, September 6, 2010 5:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
Nice question, I got it right.

I did want to point out that since the correct answer is that it will generate an error, no rows will be displayed because of that error. it could then be argued that the "No rows will be displayed" answer could be considered a correct choice as well.

Post #981066
Posted Monday, September 6, 2010 9:35 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 17,812, Visits: 15,737
Thanks for the question



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #981159
Posted Tuesday, September 7, 2010 7:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 2,389, Visits: 2,779
Good question, thanks. I have caused this error many times while composing my queries, so when I realized there was an alias in the GROUP BY clause, I figured that had to be the answer.

- webrunner

(Edit) P.S. Thanks, Hugo, for the detailed explanation of why this error happens.


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #981593
Posted Tuesday, September 7, 2010 10:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:12 AM
Points: 7,791, Visits: 9,545
Nice question. And nice description of logical order from Hugo.

Tom
Post #981699
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse