InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 19:14:55 GMT20http://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxWas the typo intentional? I know Attention to detail is important for the DBA - but intentional typo's are bad form.Tue, 02 Jun 2009 05:15:57 GMTNeal-564363http://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxeasy one! option 1 is the only answer that satisfies the ORDER BY!Mon, 01 Jun 2009 04:31:41 GMTVM-723206http://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxThe question was edited to include an ORDER BY. Questions get edited if mistakes are found.Fri, 29 May 2009 10:50:50 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxThe problem is that the order by clause was left out on the initail page.Today's Question: What is the output for the query below? CREATE TABLE E (eid int, mid int, en char(10) )INSERT E SELECT 1,2,'Vyas'INSERT E SELECT 2,3,'Mohan'INSERT E SELECT 3,NULL,'Shobha'INSERT E SELECT 4,2,'Shridhar'INSERT E SELECT 5,2,'Sourabh'SELECT t1.en [Em], t2.en [Ma] FROM E t1, E t2 WHERE t1.mid = t2.eidwhen you click the "Click Here" the question has an additional line with the order by clasueCREATE TABLE E (eid int, mid int, en char(10) )INSERT E SELECT 1,2,'Vyas'INSERT E SELECT 2,3,'Mohan'INSERT E SELECT 3,NULL,'Shobha'INSERT E SELECT 4,2,'Shridhar'INSERT E SELECT 5,2,'Sourabh' SELECT t1.en [Em], t2.en [Ma] FROM E t1, E t2 WHERE t1.mid = t2.eid order by t1.enFri, 29 May 2009 10:26:30 GMTmyates7http://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspx[quote][b]Barry McConnell (5/27/2009)[/b][hr][quote][b]SanjayAttray (5/27/2009)[/b][hr]WOW.........never seen so many complaints for a single question on QOD before. Good job Reddy. You made all forum posters stand united.[/quote]You must not have seen the MDX Except question then! :w00t:[/quote]Yep Barry. Look like I missed that fun before.Thu, 28 May 2009 11:51:38 GMTSanjayAttrayhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxfirst Row- Mohan Shobha Second Row-Shridhar Mohan third Row- Sourabh Mohan Fourth Row- Vyas MohanThu, 28 May 2009 05:20:10 GMTsudhirjunehttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxHi GuysThis answer is correct when there is no order by clause in the question.row1 - Vyas Mohan row2 - Mohan Shobha row3 - Shridhar Mohan row4 - Sourabh Mohan elsethis is correct.Row 1 - Mohan Shobha | Row 2 - Shridhar Mohan | Row 3 - Sourabh Mohan | Row 4 - Vyas MohanThu, 28 May 2009 00:04:47 GMTckumareddyhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxI too have seleted option 3 hence wrong.... BAD LUCK!!I do agree with STEVE JONES; we need to be positive why people are more worried about points more than that wright or wrong? I liked positive approach of "ppcx" by giving suggestion.All are human beings and made mistakes... We are not COMPUTERS!We need to encourage people to post more and wright about SQL. SUCCESS COMES OUT OF FAILURE ONLY.Wed, 27 May 2009 22:46:36 GMTfree_mascothttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspx[quote][b]Steve Jones - Editor (5/27/2009)[/b][hr]The newsletter is what the question goes out as, but it could potentially be changed if we find issues. I'm not sure what you want. Do you want us to correct questions or not? It we correct them, the newsletter will be different.[/quote]How about a friendly suggestion to indicate on the web page that the question is different from what was mailed out? Maybe right above the list of answer choices? I like the QOTD's. They've helped me learn more about MSSQL.Wed, 27 May 2009 15:35:15 GMTppcxhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspx[quote][b]sjsubscribe (5/27/2009)[/b][hr]Joins in ANSI syntax can be made explicit without mangling the where logic. Parsers and optimizations have less ambiguity to deal with under certain conditions. The old syntax of using *= for left and right joins can now be done away. Cross joins also become explicit. For programming, the cleaner and more explicit ANSI syntax could lead to faster debugging. It took me a while to transition, but now that I did, I like it.[/quote]I feel lucky... when I got involved with SQL Server, DBs, and DB Theory ANSI style joins were available. I actually had queries back on SQL 7 that performed better when I specified ANSI joins than when I used that kack nasty syntax (for whatever reason, but I was seeing actual differences in performance back then).I remember looking at a 12 table join in the older where clause syntax and spending quite a bit of time trying to figure out how all the joins worked. The first thing I did after I figured it out was to convert the query to ANSI syntax. For a few years I was teaching Intro to DB Theory at a local community college. I explained the Where clause Join and told my students if they ever turned in work using it that I'd take off points for bad style.Wed, 27 May 2009 15:33:34 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspx[quote][b]cj_logan (5/27/2009)[/b][hr]Question Why is the inner join the recommended option ? I know Microsoft recommends this way but no where can I find a reason ? According to the execution plan both are the same and both cost 50%SELECT t1.en [Em], t2.en [Ma] FROM E t1, E t2 WHERE t1.mid = t2.eid order by t1.enSELECT t1.en [Em], t2.en [Ma] FROM E t1 inner join E t2 on t1.mid = t2.eid order by t1.en[/quote]Joins in ANSI syntax can be made explicit without mangling the where logic. Parsers and optimizations have less ambiguity to deal with under certain conditions. The old syntax of using *= for left and right joins can now be done away. Cross joins also become explicit. For programming, the cleaner and more explicit ANSI syntax could lead to faster debugging. It took me a while to transition, but now that I did, I like it.Wed, 27 May 2009 15:24:33 GMTsjsubscribehttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspx[quote][b]Mark Pratt (5/27/2009)[/b][hr]I don't look at these challenges much. Today's item doesn't encourage me to do so in the future.[/quote]I used to be the same, but now I try to figure out what the questioner is trying to test. I've learned a lot from these questions. I can be 'morally' right and still lose the point - I've still figured something out.Wed, 27 May 2009 15:16:03 GMTTom Brownhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxPlease supply the right list of options when you post a question.the correct answer isEm MaVyas Mohan Mohan Shobha Shridhar Mohan Sourabh MohanWed, 27 May 2009 15:09:57 GMTfiona_fernhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxWhen someone said they ran the SQL, I said with the corrected question they couldn't get those results.The newsletter is what the question goes out as, but it could potentially be changed if we find issues. I'm not sure what you want. Do you want us to correct questions or not? It we correct them, the newsletter will be different.Wed, 27 May 2009 15:03:09 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspx[quote][b]cj_logan (5/27/2009)[/b][hr]Question Why is the inner join the recommended option ? I know Microsoft recommends this way but no where can I find a reason ? According to the execution plan both are the same and both cost 50%SELECT t1.en [Em], t2.en [Ma] FROM E t1, E t2 WHERE t1.mid = t2.eid order by t1.enSELECT t1.en [Em], t2.en [Ma] FROM E t1 inner join E t2 on t1.mid = t2.eid order by t1.en[/quote]Because that is part of the ANSI standard for SQL. The older syntax should be phased out. The argument goes that the new syntax is cleaner by separating the JOIN logic from the filter (WHERE) logic.Wed, 27 May 2009 14:48:56 GMTBarry McConnellhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspx[quote][b]SanjayAttray (5/27/2009)[/b][hr]WOW.........never seen so many complaints for a single question on QOD before. Good job Reddy. You made all forum posters stand united.[/quote]You must not have seen the MDX Except question then! :w00t:Wed, 27 May 2009 14:44:32 GMTBarry McConnellhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxQuestion Why is the inner join the recommended option ? I know Microsoft recommends this way but no where can I find a reason ? According to the execution plan both are the same and both cost 50%SELECT t1.en [Em], t2.en [Ma] FROM E t1, E t2 WHERE t1.mid = t2.eid order by t1.enSELECT t1.en [Em], t2.en [Ma] FROM E t1 inner join E t2 on t1.mid = t2.eid order by t1.enWed, 27 May 2009 14:17:39 GMTcj_loganhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxWOW.........never seen so many complaints for a single question on QOD before. Good job Reddy. You made all forum posters stand united.Wed, 27 May 2009 13:18:06 GMTSanjayAttrayhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspx[quote][b]Barry McConnell (5/27/2009)[/b][hr]I typically read the email, solve the problem in my head, click the link and select the answer I got.[/quote]That's my MO, too. I don't expect there to be a difference between the newsletter and the website.Wed, 27 May 2009 12:43:05 GMTRML51http://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxI typically read the email, solve the problem in my head, click the link and select the answer I got. Now you're saying I have to ignore the email because what is in there doesn't reflect the actual question? Sounds like a weasel answer to me. Just admit the question was not properly edited in both the content or the alternative presentations and move on.Wed, 27 May 2009 11:47:46 GMTBarry McConnellhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxYou're right, my response was somewhat whiny given that the original post was most likely a keyboard error, not a logic error. I'll save my criticisms for when I've accumulated enough points to be classified something beyond Grasshopper.Wed, 27 May 2009 11:14:11 GMTJustANumberhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspx[quote][b]Steve Jones - Editor (5/27/2009)[/b][hr]The corrected question, which is on the web site, is[code]CREATE TABLE E (eid int, mid int, en char(10) )INSERT E SELECT 1,2,'Vyas'INSERT E SELECT 2,3,'Mohan'INSERT E SELECT 3,NULL,'Shobha'INSERT E SELECT 4,2,'Shridhar'INSERT E SELECT 5,2,'Sourabh' SELECT t1.en [Em], t2.en [Ma] FROM E t1, E t2 WHERE t1.mid = t2.eid order by t1.en[/code]There is no way you can get "Vyas Mohan" first.Running the SQL from the newsletter kind of defeats the purpose in answering. The idea is for you to use your knowledge to figure it out.[/quote]Sure you can... read it from top down without the ORDER BY.eid 1 gives you Vyas which joins to eid 2 and gets MohanVyas Mohanfollowed by Mohan eid 2 joining to eid 3 and gettingMohan Shobhaif you read the email, figured out what you were doing, and then clicked the link, and answered the question (because you didn't see the order by.. on my machine it was hidden by a scrollbar at first), you'd easily get Vyas Mohan first.Wed, 27 May 2009 10:59:31 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxArgh! The question changed from the email and I didn't read the web page's version of the question!Wed, 27 May 2009 10:55:50 GMTppcxhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxGoes to prove that you get hanged by the same clever rope you wove.Wed, 27 May 2009 10:53:48 GMTsjsubscribehttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxThe corrected question, which is on the web site, is[code]CREATE TABLE E (eid int, mid int, en char(10) )INSERT E SELECT 1,2,'Vyas'INSERT E SELECT 2,3,'Mohan'INSERT E SELECT 3,NULL,'Shobha'INSERT E SELECT 4,2,'Shridhar'INSERT E SELECT 5,2,'Sourabh' SELECT t1.en [Em], t2.en [Ma] FROM E t1, E t2 WHERE t1.mid = t2.eid order by t1.en[/code]There is no way you can get "Vyas Mohan" first.Running the SQL from the newsletter kind of defeats the purpose in answering. The idea is for you to use your knowledge to figure it out.Wed, 27 May 2009 10:49:35 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspx[quote][b]Laurie Dunn (5/27/2009)[/b][hr]Yup, I also ran it in SQL and got:row1 - Vyas Mohan row2 - Mohan Shobha row3 - Shridhar Mohan row4 - Sourabh Mohan So I just tried to pick an answer that was close so I could see he discussion. Is there a specific way to approach this in order to get the listed answer?[/quote]Yup, don't forget the ORDER BY. The email that went out was lacking it. So if you answered based on the email you get the above order, else you would get the correct answer per the question.Wed, 27 May 2009 10:45:45 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxYup, I also ran it in SQL and got:row1 - Vyas Mohan row2 - Mohan Shobha row3 - Shridhar Mohan row4 - Sourabh Mohan So I just tried to pick an answer that was close so I could see he discussion. Is there a specific way to approach this in order to get the listed answer?Wed, 27 May 2009 10:29:40 GMTLaurie Dunnhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspx[quote][b]Rachel Byford (5/27/2009)[/b][hr]What a lot of complainers!If everybody who had posted came up with a question of their own I bet we'd have some really good questions. And they'd all be perfect of course!:-)[/quote]Hear, Hear!I always look forward to the QOD, even when I guess incorrectly.Wed, 27 May 2009 10:27:30 GMT Chad Crawfordhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspx[quote][b]Mark Pratt (5/27/2009)[/b][hr]I don't look at these challenges much. Today's item doesn't encourage me to do so in the future.[/quote]Talk about quick to judge... One typo'd question and the whole of QOD is written off. That seems a little harsh to me, considering the number of interesting little snippets I've picked up from the various QOD's over the last few months.Wed, 27 May 2009 10:19:17 GMTAndeavourhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspx[quote][b]CZakraysek (5/27/2009)[/b][hr]Also really stupid to use such typo freindly names: just use A, B, C if you can't bring yourself to use Jim , Tim, Pat, etc.[/quote]I suspect they're only typo-friendly (sic) to Europeans and Americans. Jim and Tim may be more typo-inducing for Indian forum members. This is a worldwide group.Wed, 27 May 2009 10:12:08 GMTScott-144766http://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxAnd the Particular Order is what makes this question non-trivial, the correct set in the wrong order is a wrong anwser.Wed, 27 May 2009 10:09:38 GMTCZakraysekhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxIn eMail no ORDER BY Clause, so people may be getting it wrong - Also really stupid to use such typo freindly names: just use A, B, C if you can't bring yourself to use Jim , Tim, Pat, etc.Wed, 27 May 2009 10:06:50 GMTCZakraysekhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxI don't look at these challenges much. Today's item doesn't encourage me to do so in the future.Wed, 27 May 2009 10:06:49 GMTJustANumberhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxThe question is repeated when you click in the newsletter. If there is an issue, we will try to fix it as soon as we can.The ORDER BY doesn't change what might occur, it just enforces a particular order.Wed, 27 May 2009 10:04:53 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspx[quote][b]rkavula (5/27/2009)[/b][hr]Hi , the answer for this quesiton is belowSELECT t1.en [Em], t2.en [Ma] FROM E t1, E t2 WHERE t1.mid = t2.eid Em MaVyas Mohan Mohan Shobha Shridhar Mohan Sourabh Mohan select t1.en [Em], t2.en [Ma] FROM E t1 Inner join E t2 on t1.mid = t2.eid -- same out put[/quote]I got this one wrong too... I ran the query out of the email...except the query posted is a bit different[code]CREATE TABLE E (eid int, mid int, en char(10) )INSERT E SELECT 1,2,'Vyas'INSERT E SELECT 2,3,'Mohan'INSERT E SELECT 3,NULL,'Shobha'INSERT E SELECT 4,2,'Shridhar'INSERT E SELECT 5,2,'Sourabh' SELECT t1.en [Em], t2.en [Ma] FROM E t1, E t2 WHERE t1.mid = t2.eid order by t1.en[/code]the order by changes it.Wed, 27 May 2009 09:50:37 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxWOW,:w00t: I got a point back. Thanks Steve :-DI appreciate it's your morning there and there may be more pressing concerns than a point for a QOD answer.Wed, 27 May 2009 09:37:34 GMTAndeavourhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspx[quote][b]Steve Jones - Editor (5/27/2009)[/b][hr]The question has been corrected to fix the typo and include an ORDER by clause.main.denis, sorry we are not responding fast enough for you. It is the morning here, this question has been out for a few hours, and unfortunately, it is not our top priority.[/quote] Yes, but I took the question as in the e-mail and hence answered option 3, which seemed correct. Now with the order by clause, of course there is a change.... What gives.Wed, 27 May 2009 09:29:29 GMTRichard M.http://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxThe question has been corrected to fix the typo and include an ORDER by clause.main.denis, sorry we are not responding fast enough for you. It is the morning here, this question has been out for a few hours, and unfortunately, it is not our top priority.Wed, 27 May 2009 09:08:59 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspx[quote][b]R. Brush (5/27/2009)[/b][hr]Should row numbers be specified in the answer if no ORDER BY clause is given? It's my understanding that a SELECT statement without an ORDER BY clause, returns the set, but with no guaranteed order. Fortunately, in the answer options for this question, there was only one that had all the right pairs.[/quote]That has been discussed before and apparently ignored or overlooked.Wed, 27 May 2009 08:43:21 GMTAlvin Ramardhttp://www.sqlservercentral.com/Forums/Topic723722-1556-1.aspxShould row numbers be specified in the answer if no ORDER BY clause is given? It's my understanding that a SELECT statement without an ORDER BY clause, returns the set, but with no guaranteed order. Fortunately, in the answer options for this question, there was only one that had all the right pairs.Wed, 27 May 2009 08:38:51 GMTR. Brush