Sales puzzle

  • imihaela

    SSC Enthusiast

    Points: 121

    Thanks.

    I have learnt also today about "order by [number]"

    kr,

    Mihaela

  • kaspencer

    SSCarpal Tunnel

    Points: 4252

    Thanks alot to Dinesh, Tom and Hany for taking the trouble to answer my little question. Odd that I hadn't come across it before, but there we are!

    All the best

    Ken.

    You never know: reading my book: "All about your computer" might just tell you something you never knew!
    lulu.com/kaspencer

  • Mike Hays

    SSCommitted

    Points: 1871

    Good Question, made me think...

  • Luis Cazares

    SSC Guru

    Points: 183572

    tom.w.brannon (8/14/2013)


    I use numbers in my order by statements every day for short queries. It is faster than typing out long names and is especially nice when the column is some kind of calculatoni, function or case statement. I would not use them for production systems but for just checking around it my data they have been wonderful

    For calculation, function or case statement, you can use the column alias to have a simple and clear way to reference a column in the order by.:-)

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • tom.w.brannon

    Hall of Fame

    Points: 3956

    Unfortunately using the alias in the order by clause does not work in all flavors of SQL and I have trouble rememberig which it does work with. Agreed, it is clearer to use the alias when you can.

  • Koen Verbeeck

    SSC Guru

    Points: 258950

    Nice question, thanks.

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

  • mtassin

    SSC-Insane

    Points: 23096

    Good Question. Made my brain engage for a moment or three... now sadly... I must return to the land of MDS.



    --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]

  • MikeSpike

    SSC Enthusiast

    Points: 128

    But why is the result (0, 1) eliminated?

    Does the engine first pick cp.pkey=0 from row 1 and 2 then looks at row 3-4 to check if up.pkey matches 1 and thus only returns row 4 (1,1)?

    name name up.pkey cp.pkey

    AAA AAA 0 0

    BBB AAA 1 0

    AAA BBB 0 1

    BBB BBB 1 1

  • Pete Cox

    SSChasing Mays

    Points: 636

    I would have thought the "order by" clause is absoutely necessary to guarantee a match to the sequence supplied as the answer. There is no implicit row order involved in the "select". Or have I got something wrong ?

  • MikeSpike

    SSC Enthusiast

    Points: 128

    Pete Cox (8/15/2013)


    I would have thought the "order by" clause is absoutely necessary to guarantee a match to the sequence supplied as the answer. There is no implicit row order involved in the "select". Or have I got something wrong ?

    That was my thought too, but then it occurred to me that the ORDER BY is applied as the final step and has nothing to do with WHERE-logic.

  • Knut Boehnert

    SSCrazy

    Points: 2946

    MikeSpike (8/15/2013)


    But why is the result (0, 1) eliminated?

    Does the engine first pick cp.pkey=0 from row 1 and 2 then looks at row 3-4 to check if up.pkey matches 1 and thus only returns row 4 (1,1)?

    name name up.pkey cp.pkey

    AAA AAA 0 0

    BBB AAA 1 0

    AAA BBB 0 1

    BBB BBB 1 1

    Had to think that through. It's a nice way to write an XOR in a way.

    Or reverse the logic with a NOT and it becomes:

    WHERE NOT (up.key = 0 AND cp.key = 1)

    Maybe this makes it clearer.

  • Primo Dang

    SSCrazy

    Points: 2643

    Knut Boehnert (8/15/2013)


    MikeSpike (8/15/2013)


    But why is the result (0, 1) eliminated?

    Does the engine first pick cp.pkey=0 from row 1 and 2 then looks at row 3-4 to check if up.pkey matches 1 and thus only returns row 4 (1,1)?

    name name up.pkey cp.pkey

    AAA AAA 0 0

    BBB AAA 1 0

    AAA BBB 0 1

    BBB BBB 1 1

    Had to think that through. It's a nice way to write an XOR in a way.

    Or reverse the logic with a NOT and it becomes:

    WHERE NOT (up.key = 0 AND cp.key = 1)

    Maybe this makes it clearer.

    Some may find this syntax even clearer:

    WHERE up.pkey <> 0 AND cp.pkey <> 1

    Edit: couldn't get the <> operator to show correctly in the code block, so I just got rid of the code tag

  • RLilj33

    SSCrazy

    Points: 2146

    Primo Dang (8/15/2013)


    Knut Boehnert (8/15/2013)


    MikeSpike (8/15/2013)


    But why is the result (0, 1) eliminated?

    Does the engine first pick cp.pkey=0 from row 1 and 2 then looks at row 3-4 to check if up.pkey matches 1 and thus only returns row 4 (1,1)?

    name name up.pkey cp.pkey

    AAA AAA 0 0

    BBB AAA 1 0

    AAA BBB 0 1

    BBB BBB 1 1

    Had to think that through. It's a nice way to write an XOR in a way.

    Or reverse the logic with a NOT and it becomes:

    WHERE NOT (up.key = 0 AND cp.key = 1)

    Maybe this makes it clearer.

    Some may find this syntax even clearer:

    WHERE up.pkey <> 0 AND cp.pkey <> 1

    Edit: couldn't get the <> operator to show correctly in the code block, so I just got rid of the code tag

    Thank you MikeSpike for the question and Knut Boehner & Primo Dang for your responses. That was my question as well and this helped to clarify the correct answer for me.

  • Pete Cox

    SSChasing Mays

    Points: 636

    I was not proposing that the WHERE and the ORDER BY were cooperating in some way to get the 3 values of the result. Those 3 values match the 3 values of the supplied answer solely as a result of the WHERE clause. I was trying, poorly, to point out that the exact order of those values can only be guaranteed to match the order of the supplied answer if we have the ORDER BY clause. An earlier post had suggested that the ORDER BY was unnecessary, I disagreed. 🙂

  • twin.devil

    SSC-Insane

    Points: 22208

    really nice question ... Thanks 🙂

Viewing 15 posts - 16 through 30 (of 33 total)

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