Assigning categories to values 1

Question

Post reply

Assigning categories to values 1

Hugo Kornelis

SSC Guru

Points: 64790
More actions
May 13, 2013 at 9:49 pm

#293199

Comments posted to this topic are about the item Assigning categories to values 1
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
May 14, 2013 at 1:51 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 71 total)

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

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 1

Ouch - interesting question thank you

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Alex Fekken Ten Centuries Points: 1109 More actions · Answer 2

Nice one. Would have had it wrong if I hadn't expected a "catch".

Jamsheer Ten Centuries Points: 1136 More actions · Answer 3

Simple and interesting one. Got it right.. Thanks....

Lokesh Vij SSChampion Points: 10836 More actions · Answer 4

Lucky enough to find the anomaly instead of getting into the trap of "BETWEEN (high) AND (low)" which should be otherwise 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 5

Good one Hugo.

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

sqlnaive SSCoach Points: 17437 More actions · Answer 6

This was simpy amazing. Simple question yet interesting. It looked plain and simple at first but was just curious to find what was fishy and then i concentrated on the very part.

Nice tricky question. 🙂

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 7

Really Good one, Hugo, thank you for posting (so basic and so much of valuable point)

(Not sure and can't think of why one would use as "BETWEEN -1 AND -10" with "start" as the higher value in any practical case. When I saw -1 as the start value.. I thought that might be the catch here and .... 🙂 )

SELECT KeyCol, ValueCol, CASE

WHEN ValueCol < -10 THEN 'Very negative'

WHEN ValueCol BETWEEN -10 AND -1 THEN 'Bit negative'

WHEN ValueCol = 0 THEN 'Zero'

WHEN ValueCol BETWEEN 1 AND 10 THEN 'Bit positive'

ELSE 'Very positive'

END AS Category

FROM

(

SELECT 1 [KeyCol], 0 [ValueCol]

UNION ALL

SELECT 2, 4

UNION ALL

SELECT 3, -15

UNION ALL

SELECT 4, 14

UNION ALL

SELECT 5, -2

) A

ORDER BY 2

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

WWDMark Hall of Fame Points: 3157 More actions · Answer 8

Liked that one, looked innocuous at first then had a think about it and had a facepalm moment as I was writing out what I thought the results would be and got on the BETWEEN statement! Great question, glad I took some time over it. 😀

MCITP
MCTS - E-Business Card
Twitter: WWDMark

Try not! Do or do not, there is no try

email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk

Alex Fekken Ten Centuries Points: 1109 More actions · Answer 9

Not sure and can't think of why one would use as "BETWEEN -1 AND -10" with "start" as the higher value in any practical case.

Depends on where you are coming from: my initial thought, when I first encountered 'BETWEEN', was "I can't think why one would use BETWEEN in any practical case.".

BETWEEN suffers from at least two ambiguities until you consult the "official" definition to find out what arbitrary choices have been made to remove them.

The first is the issue in question: it would have made just as much sense, if not more, to make the definition symmetrical with respect to the two bounds. Because [1] it would be more work to write down the alternative expression and [2] the keyword AND in all other contexts implies symmetry of the operands, unlike e.g. 'x BETWEEN a TO b'.

The second ambiguity is whether BETWEEN includes or excludes the bounds. Intuitively I would make the wrong assumption there as well.

So for me it is a bad language feature, well-deserving of at least one Question of the Day.

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 10

Good one.

Thanks!

IgorMi

Igor Micev,My blog: www.igormicev.com

kapil_kk SSC-Insane Points: 21316 More actions · Answer 11

Good question Hugo 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 12

Alex Fekken (5/14/2013)
Not sure and can't think of why one would use as "BETWEEN -1 AND -10" with "start" as the higher value in any practical case.
Depends on where you are coming from: my initial thought, when I first encountered 'BETWEEN', was "I can't think why one would use BETWEEN in any practical case.".
BETWEEN suffers from at least two ambiguities until you consult the "official" definition to find out what arbitrary choices have been made to remove them.
The first is the issue in question: it would have made just as much sense, if not more, to make the definition symmetrical with respect to the two bounds. Because [1] it would be more work to write down the alternative expression and [2] the keyword AND in all other contexts implies symmetry of the operands, unlike e.g. 'x BETWEEN a TO b'.
The second ambiguity is whether BETWEEN includes or excludes the bounds. Intuitively I would make the wrong assumption there as well.
So for me it is a bad language feature, well-deserving of at least one Question of the Day.

... even if AND is replaced with TO ... it still works in the same way... and when you know the BETWEEN included the bounds, why you want to use your "intuition" and then make the "wrong assumption"? (I have used this keyword and never thought of going alternate.. and BETWEEN works as it suppose to work)

(my concern is with the start expr with the higher value and then put lower value to the end expr and I don't see any practical case where and how it is used to make the code work...)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

honza.mf SSCertifiable Points: 5519 More actions · Answer 13

Raghavendra Mudugal (5/14/2013)
(Not sure and can't think of why one would use as "BETWEEN -1 AND -10" with "start" as the higher value in any practical case. When I saw -1 as the start value.. I thought that might be the catch here and .... 🙂 )

I can think one can copy-paste positive thresholds and write minus signs afterwards.