The Lonely and Neglected Cartesian Product (Cross Join)

  • 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!

  • +2 for me too. Thanks for question.

    --Angad Singh
    If I Stop Learning, I Cease to Be A SIKH !

  • 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:

  • 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

  • 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:

  • 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

  • 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

  • Thank you, Thomas - and keep them comin'!

  • 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,

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

  • Thanks for the question and the nice explanation

  • Greate question.... I like this one. Thanks!



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • +1

    We want more!

  • Interesting question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 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

  • 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 35 total)

You must be logged in to reply to this topic. Login to reply