Sales puzzle

Question

Post reply

Sales puzzle

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

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

imihaela SSC Enthusiast Points: 121 More actions · Answer 1

Thanks.

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

kr,

Mihaela

kaspencer SSCarpal Tunnel Points: 4869 More actions · Answer 2

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 More actions · Answer 3

Mike Hays

SSCommitted

Points: 1871

August 14, 2013 at 7:22 am

#1641376

Good Question, made me think...

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

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 More actions · Answer 5

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: 259085 More actions · Answer 6

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: 23099 More actions · Answer 7

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 More actions · Answer 8

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

Pete Cox SSChasing Mays Points: 636 More actions · Answer 9

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 More actions · Answer 10

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: 2951 More actions · Answer 11

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.

Primo Dang SSCrazy Points: 2643 More actions · Answer 12

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

RLilj33 SSCrazy Points: 2153 More actions · Answer 13

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.

Pete Cox SSChasing Mays Points: 636 More actions · Answer 14

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

Umer Akram SSC-Insane Points: 22209 More actions · Answer 15

Umer Akram

SSC-Insane

Points: 22209

August 16, 2013 at 2:04 am

#1642011

really nice question ... Thanks 🙂