February 12, 2013 at 5:21 am
archie flockhart (2/12/2013)
I selected "none of these are correct".The first two are wrong for the reasons stated in the answer.
The third is wrong because the requirements are that the three rows for each person are produced with a 1 on the first row, 2 on the second, and 3 on the third. There is nothing in the proposed "third query attempt" which guarantees this ordering within the rows for each individual.
It sorts by t1.pk (and attempts to sort at a lower level by t1.column1 , although there can be no circumstances where two rows in the output have different values for t1.column1 but the same values for t1.pk, since pk is defined as the primary key)
This guarantees that the three rows for each person are listed together, but does not sort them into any particular order. You might get the order you want, but you can't be sure.
I guess it is a slight error in that it would I think be better to use t2.PK as the second sort term, although in practice it is accepted that the query will work as written with SQL Server.
On the second question many people spend adequate time on duty with their colleagues and may find it onerous to spend more in those circumstances. These people are called curmudgeons and I confess to being one of them. Still, even I could probably handle a dinner dance or two, sounds quite civilised!
February 12, 2013 at 5:51 am
+2 for me too. Thanks for question.
--Angad Singh
If I Stop Learning, I Cease to Be A SIKH !
February 12, 2013 at 6:14 am
Nice question! Glad to see one involving cross joins.
archie flockhart (2/12/2013)
I selected "none of these are correct".The first two are wrong for the reasons stated in the answer.
The third is wrong because the requirements are that the three rows for each person are produced with a 1 on the first row, 2 on the second, and 3 on the third. There is nothing in the proposed "third query attempt" which guarantees this ordering within the rows for each individual.
It sorts by t1.pk (and attempts to sort at a lower level by t1.column1 , although there can be no circumstances where two rows in the output have different values for t1.column1 but the same values for t1.pk, since pk is defined as the primary key)
This guarantees that the three rows for each person are listed together, but does not sort them into any particular order. You might get the order you want, but you can't be sure.
Wow, I completely missed that. :blink:
February 12, 2013 at 8:25 am
This case is simpler: NO CROSS JOIN NEEDED.
Get result in text format:
create table #temp1(PK int IDENTITY Primary Key, column1 varchar(20))
insert into #temp1 values ('employee 1')
insert into #temp1 values ('employee 2')
insert into #temp1 values ('employee 3')
insert into #temp1 values ('employee 4')
insert into #temp1 values ('employee 5')
SELECT '1' + ' ' +column1
+ CHAR(13)+ CHAR(10) +'2'
+ CHAR(13)+ CHAR(10) +'3'
FROM #temp1
ORDER BY PK
February 12, 2013 at 8:29 am
archie flockhart (2/12/2013)
I selected "none of these are correct".
The third is wrong because the requirements are that the three rows for each person are produced with a 1 on the first row, 2 on the second, and 3 on the third. There is nothing in the proposed "third query attempt" which guarantees this ordering within the rows for each individual.
+1
I also selected the same answer for the same reason. I guess this has been repeatedly mentioned in discussions for previous Qotds that indexing (clustered or non clustered) doesn't guarantee order of results in select statements on the indexed column until explicity mentioned by an order by clause (at the least it's not a documented behaviour). I guess the order by clause in the mentioned answer should be changed to order by t1.PK, 'Signature Column' DESC
Good question... although I definetly want my point back... 😀
___________________________________________________________________
If I can answer a question then anyone can answer it..trying to reverse the logic.. :hehe:
February 12, 2013 at 10:00 am
The correct answer is something like "none of these is correct but there's a fair probability of getting the right result most of the time with query 3 since the dataset in t2 is small enough that the physical order and logical order and scan order are probably all the same" - of course that means that just "order by t1.PK is all that's required (and that's the only reason this worked, the rest of the order clause in query 3 is meaningless junk). I debated going for for none correct, but decided that probably whoever set the question had run that query and got the required result and assumed that that meant it would always give the required result, so chose what I knew to be a wrong answer and acquired the 2 points. To guarantee getting the required result requires a different order by clause, either addressing the order directly in terms of the resulting signature column (order by t1.PK,'Signature Column' desc) or through the structure of the table t2 (order by t1.PK,t2.PK) as suggested by Toreador.
So "and sorting is correct" in the explanation is just plain wrong.
Despite that, it's quite a nice question. If the order clause had been right, it would have been a great question.
Tom
February 12, 2013 at 10:15 am
Carlo Romagnano (2/12/2013)
This case is simpler: NO CROSS JOIN NEEDED.Get result in text format:
create table #temp1(PK int IDENTITY Primary Key, column1 varchar(20))insert into #temp1 values ('employee 1')
insert into #temp1 values ('employee 2')
insert into #temp1 values ('employee 3')
insert into #temp1 values ('employee 4')
insert into #temp1 values ('employee 5')
SELECT '1' + ' ' +column1
+ CHAR(13)+ CHAR(10) +'2'
+ CHAR(13)+ CHAR(10) +'3'
FROM #temp1
ORDER BY PK
Well, it's certainly not an illustration of the power of cross joins to simplify complex problems, but it is a nice illustration of how cross join works.
Tom
February 12, 2013 at 10:25 am
Thank you, Thomas - and keep them comin'!
February 12, 2013 at 11:06 am
Excellent question and explanation.
Regarding the second question, here in Mexico there are still several companies that sponsor year-end parties, most of them for employees only, and some for employees with spouses/partners/significant others. There are even some companies that throw kid's parties for all employees' children. and yes, I like those.
Saludos,
February 12, 2013 at 11:19 am
Thanks for the question and the nice explanation
February 12, 2013 at 11:56 am
Greate question.... I like this one. Thanks!
February 12, 2013 at 1:52 pm
+1
We want more!
February 13, 2013 at 1:37 am
Interesting question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 13, 2013 at 5:32 am
Thanks for a great question with some interesting points bought up in the discussion
Dan
MCTS | MCITP | Microsoft SQL Server 2008 Administration & Development
MCSA | MCSE | Business Intelligence SQL Server 2012
February 13, 2013 at 6:15 am
One of the best QOTD's I've seen in a long time, with a great explanation and code setup. Thanks.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply