Group by

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Manie Verster » Group by

27 posts, Page 2 of 3

««1 2 3 »»

Group by

Rate Topic

Display Mode

Topic Options

Author

Message

tommyh

Posted Monday, September 06, 2010 3:12 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:15 AM
Points: 1,476, Visits: 1,943

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 06, 2010 3:30 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 11, 2013 12:58 AM
Points: 174, Visits: 555

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

Hugo Kornelis

Posted Monday, September 06, 2010 3:37 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:49 AM
Points: 5,235, Visits: 7,035

Open Minded (9/6/2010)

I got the answer right, after a long series of wrongs.

Hugo Kornelis (9/6/2010)

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

Koen Verbeeck

Posted Monday, September 06, 2010 4:05 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 9:06 AM
Points: 9,367, Visits: 6,465

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

AmarJhingan

Posted Monday, September 06, 2010 4:42 AM

Forum 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

tommyh

Posted Monday, September 06, 2010 4:52 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:15 AM
Points: 1,476, Visits: 1,943

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

cengland0

Posted Monday, September 06, 2010 5:55 AM

Ten 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

SQLRNNR

Posted Monday, September 06, 2010 9:35 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 18,733, Visits: 12,332

Thanks for the question

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

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #981159

webrunner

Posted Tuesday, September 07, 2010 7:55 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:05 PM
Points: 2,117, Visits: 2,209

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.

-------------------
"The chemistry must be respected." - Walter White

"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

L' Eomot Inversé

Posted Tuesday, September 07, 2010 10:30 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:54 AM
Points: 7,086, Visits: 7,138

Nice question. And nice description of logical order from Hugo.

Tom
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)

Post #981699

« Prev Topic | Next Topic »

27 posts, Page 2 of 3

««1 2 3 »»

Permissions