August 14, 2013 at 7:17 am
Thanks.
I have learnt also today about "order by [number]"
kr,
Mihaela
August 14, 2013 at 7:18 am
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
August 14, 2013 at 7:22 am
Good Question, made me think...
August 14, 2013 at 8:16 am
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.:-)
August 14, 2013 at 8:58 am
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.
August 14, 2013 at 11:04 am
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 14, 2013 at 12:13 pm
Good Question. Made my brain engage for a moment or three... now sadly... I must return to the land of MDS.
August 15, 2013 at 1:30 am
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
August 15, 2013 at 2:05 am
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 ?
August 15, 2013 at 2:09 am
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.
August 15, 2013 at 6:47 am
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.
August 15, 2013 at 7:54 am
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
August 15, 2013 at 11:19 am
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.
August 16, 2013 at 1:54 am
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. 🙂
August 16, 2013 at 2:04 am
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