SQL Server Performance

Between X

Discussion in 'General Developer Questions' started by dineshasanka, Jan 11, 2006.

  1. dineshasanka Moderator

    I have a query which I need to return all the grades from X to X29
    When I use Between command it only returns grades X, X1 and X2. But this is working fine for other grades like A B and C it is working

    ----------------------------------------
    http://spaces.msn.com/members/dineshasanka
  2. FrankKalis Moderator

    Dinesh, you've made a classical newbie mistake. How should we suggest anything when you don't provide at least the statement you are using? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
  3. dineshasanka Moderator

    Sorry

    SELECT count(1) FROM RespondentMaster a, CountryMaster b with (NoLock)
    where a.CounNo = b.CounNo and a.Status='Y' AND a.CounNo = 64
    AND (a.Grade IN ('X', 'X0', 'X1', 'X2', 'X3', 'X4', 'X5', 'X6', 'X7', 'X8', 'X9', 'X10',
    'X11', 'X12', 'X13', 'X14', 'X15', 'X16', 'X17', 'X18', 'X19', 'X20'))

    Returns 1745 records



    SELECT count(1) FROM RespondentMaster a, CountryMaster b with (NoLock)
    where a.CounNo = b.CounNo and a.Status='Y' AND a.CounNo = 64 AND (a.Grade between 'X' and 'X20' )

    Return 992 Records. Fetches only X,X0,X1,X2

    Tell me the difference


    ----------------------------------------
    http://spaces.msn.com/members/dineshasanka
  4. Adriaan New Member

    If you're comparing alphanumerical strings, only the ASCII value of the characters is taken into consideration, not any numerical value that the string is supposed to represent.<br /><br />This means that only grades where the second character's ASCII value is &lt;= that of the "2" character and the third character's ASCII value is &lt;= that of the "0" character, are considered to be between "X" and "X20".<br /><br />Just take a better look at your results:<br /><br />* you're getting "X0", "X1", "X10", "X11" ... "X19" and "X20"<br /><br />* you're filtering out "X3" ... "X9"<br /><br />Another classical newbie mistake, I'm afraid.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />[edited for 'between X and X20', not 'between X and X2']
  5. Adriaan New Member

    By the way, to show you understand this well, now you explain to us why "X2A" is being filtered out.[8D]<br /><br />And why "X1000" would not be filtered out.[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
  6. FrankKalis Moderator

    Wish TSQL would have something similar to PHP's natsort. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
  7. dineshasanka Moderator

    I totaly agreed that between will be better for Numeric.
    However, this code was written by some body and I was asked this question.

    My questionis why this only happening to X grade not for other grades like A,B,Y,Z [B)]

    ----------------------------------------
    http://spaces.msn.com/members/dineshasanka
  8. FrankKalis Moderator

    Are you saying that a query ...BETWEEN x1 AND x20 does not return rows like x3 and x4, but a query like BETWEEN a1 AND a20 returns rows like a3 and a4?

    If so, please provide DDL, sample data and desired output. That really sounds strange.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
  9. dineshasanka Moderator

    Yes that is the case. I doubt X is soem key letter. that is why it does not return correctly.
    I will give the DDL in later time

    ----------------------------------------
    http://spaces.msn.com/members/dineshasanka
  10. FrankKalis Moderator

    Interesting...[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
  11. Madhivanan Moderator

    >>My questionis why this only happening to X grade not for other grades like A,B,Y,Z

    Are you sure?


    Declare @t table(data varchar(10))
    insert into @t
    select 'a' as data union all select 'a0' union all select 'a1' union all select 'a2' union all select 'a3'
    union all select 'a4' union all select 'a5' union all select 'a6' union all select 'a7' union all select 'a8'
    union all select 'a9' union all select 'a10' union all select 'a11' union all select 'a12' union all select 'a13'

    select * from @t
    where data between 'a1' and 'a10'

    How many rows do above return?

    If first letter is fixed, then how abt something like this?

    select * from @t where substring(data,2,len(data)) between 1 and 10


    Madhivanan

    Failing to plan is Planning to fail
  12. mmarovic Active Member

    It is clear why x3-x9 are filtered out, but x11-x19 should appear in the result set.
  13. FrankKalis Moderator

    Yes, but if I understood this correct is this behaviour not consistent. a3-a9 are not filtered out, while x3-x9 are. That's at least what I understood so far and that's what sounds really strange.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
  14. Adriaan New Member

    Do the "missing values" actually occur in the column on which you filter?

    SELECT Grade FROM RespondentMaster GROUP BY Grade
  15. Madhivanan Moderator

    Dinesh, can you highlight the changes you did in your question?

    Madhivanan

    Failing to plan is Planning to fail
  16. dineshasanka Moderator

    quote:Originally posted by Madhivanan

    Dinesh, can you highlight the changes you did in your question?


    Some body has put this a sticky note. So I removed it.

    ----------------------------------------
    http://spaces.msn.com/members/dineshasanka
  17. Adriaan New Member

    Dinesh,

    So did you find out the reason?

    Adriaan
  18. dineshasanka Moderator

  19. dineshasanka Moderator

  20. Adriaan New Member

    AFAIK, with character columns, it orders by the corresponding ASCII values for the characters contained in the string.<br /><br />Numbers "0"=48 - "9"=57<br />Capital letters "A"=65 - "Z"=90<br />Small letters "a"=97 - "z"=122.<br /><br />Interpunction is all over the range between 0 and 255. A blank space is 32.<br /><br />Diacriticals à-â-é ... well, they are all over the place and beyond.<br /><br />Presumably collation (AKA sort order[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]) also has an impact on this.<br /><br />With numerical columns, who knows ...
  21. FrankKalis Moderator

Share This Page