Assigning categories to values 1

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

  • Ouch - interesting question thank you

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

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

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

  • 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

  • Good one Hugo.

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

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

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

  • This was removed by the editor as SPAM

  • 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

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

  • Good one.

    Thanks!

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • Good question Hugo 🙂

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

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

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



    See, understand, learn, try, use efficient
    © Dr.Plch

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

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