Assigning categories to values 1

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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/

  • Ford Fairlane

    SSCertifiable

    Points: 7664

    Ouch - interesting question thank you

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • Alex Fekken

    Ten Centuries

    Points: 1109

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

  • Jamsheer

    Ten Centuries

    Points: 1136

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

  • Lokesh Vij

    SSChampion

    Points: 10836

    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

  • Danny Ocean

    SSCertifiable

    Points: 6098

    Good one Hugo.

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!
    www.GrowWithSql.com

  • sqlnaive

    SSCoach

    Points: 17435

    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

    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

  • WWDMark

    Hall of Fame

    Points: 3157

    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: www.markallen-online.co.uk
    Business Website: www.weekendwebdesign.co.uk

  • Alex Fekken

    Ten Centuries

    Points: 1109

    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: 33108

    Good one.

    Thanks!

    IgorMi

    Igor Micev,
    My blog: www.igormicev.com

  • kapil_kk

    SSC-Insane

    Points: 21316

    Good question Hugo 🙂

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

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    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

    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 72 total)

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