Sales puzzle

  • Thanks.

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

    kr,

    Mihaela

  • 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

  • Good Question, made me think...

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

  • Nice question, thanks.

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

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

  • 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)?

    namenameup.pkeycp.pkey

    AAAAAA00

    BBBAAA10

    AAABBB01

    BBBBBB11

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

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

  • 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)?

    namenameup.pkeycp.pkey

    AAAAAA00

    BBBAAA10

    AAABBB01

    BBBBBB11

    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.

  • 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)?

    namenameup.pkeycp.pkey

    AAAAAA00

    BBBAAA10

    AAABBB01

    BBBBBB11

    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

  • 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)?

    namenameup.pkeycp.pkey

    AAAAAA00

    BBBAAA10

    AAABBB01

    BBBBBB11

    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.

  • 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. 🙂

  • really nice question ... Thanks 🙂

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

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