none
Order by ASCII

    Question

  • The primary column of the table is char(10) type of key that is generated using the time based value by the client application.

    There is a requirement to order the key based on it's ASCII value. an example of how the ordered rows should look like is below.

    M8KH9oZ370

    M8KH9oZ371

    --

    --

    M8KH9oZ378

    M8KH9oZ379

    M8KH9oZ37A

    M8KH9oZ37B

    ---

    ---

    M8KH9oZ37Y

    M8KH9oZ37Z

    M8KH9oZ37a

    M8KH9oZ37b

    ---

    --

    M8KH9oZ37y

    M8KH9oZ37z

     


    ________________________________________ Mohammed Abdul Basit .NET Developer ________________________________________
    Tuesday, September 27, 2011 5:02 PM

Answers

  • Try

    declare @t table (Col varchar(100))
    insert into @t 
    select
    'M8QGm4a112'
    union all select
    'M8QGm4c134'
    union all select
    'M8QGm4W198'
    union all select
    'M8QGm4Y112'
    
    select * from @t order by Col -- regular 
    
    select * from @t ORDER BY Col COLLATE SQL_Latin1_General_Cp437_BIN -- binary (ASCII) collation
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, September 27, 2011 7:57 PM
    Moderator
  • Yes, I had to go through several links before I figured the right binary collection to use. Something MS makes it extremely difficult to find the right info in one place, you have to look though many links and they still don't have an answer.

    Try googling and see if not MS sites will show up with better and more concise explanations.

    BTW, I think I figured out the right collations after finally landing at this link

    http://msdn.microsoft.com/en-us/library/aa176551(SQL.80).aspx


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, September 27, 2011 8:24 PM
    Moderator

All replies

  • ORDER BY ASCII(right(myField,1))
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, September 27, 2011 5:04 PM
    Moderator
  • ORDER BY LEFT(someColumn, LEN(someColumn)-1), ASCII(RIGHT(someColumn, 1));
    


    MG.-
    Mariano Gomez, MIS, MCITP, PMP
    IntellPartners, LLC.
    http://www.intellpartners.com/
    Blog http://dynamicsgpblogster.blogspot.com/
    • Proposed as answer by Arbi Baghdanian Tuesday, September 27, 2011 5:25 PM
    Tuesday, September 27, 2011 5:19 PM
  • Sorry, I missed the phrase "order the key based on it's ascii value" the last time around.  It sounds to me like you need to create a couple of of computed columns and you might need to base your clustered index based on these two computed columns.  The idea of this doesn't sound so good, but you probably have a good reason for this.  Hang on and I will try to put together an example.

    EDIT:

    OK, now I don't suggest that this kind of "key" is a great idea; however, is something like this is what you are talking about:

    drop table dbo.weird_Key
    go
    create table dbo.weird_Key
    ( weird_Key char(10) not null,
      key_Seg1 as left(weird_Key, 9) persisted,
      key_seg2 as ascii(right(Weird_Key, 1)) persisted,
      constraint pk_Weird_Key primary key(key_Seg1, key_Seg2)
    )
    go
    
    insert into dbo.weird_Key
    select 'M8KH9oZ370' union all
    select 'M8KH9oZ371' union all
    select 'M8KH9oZ378' union all
    select 'M8KH9oZ379' union all
    select 'M8KH9oZ37A' union all
    select 'M8KH9oZ37B' union all
    select 'M8KH9oZ37Y' union all
    select 'M8KH9oZ37Z' union all
    select 'M8KH9oZ37a' union all
    select 'M8KH9oZ37b' union all
    select 'M8KH9oZ37y' union all
    select 'M8KH9oZ37z'
    
    --select count(weird_Key) from weird_Key            -- Returns 12
    --select count(distinct weird_Key) from weird_Key   -- Returns 8
    
    select * from weird_Key
    /* -------- Output: --------
    weird_Key  key_Seg1  key_seg2
    ---------- --------- -----------
    M8KH9oZ370 M8KH9oZ37 48
    M8KH9oZ371 M8KH9oZ37 49
    M8KH9oZ378 M8KH9oZ37 56
    M8KH9oZ379 M8KH9oZ37 57
    M8KH9oZ37A M8KH9oZ37 65
    M8KH9oZ37B M8KH9oZ37 66
    M8KH9oZ37Y M8KH9oZ37 89
    M8KH9oZ37Z M8KH9oZ37 90
    M8KH9oZ37a M8KH9oZ37 97
    M8KH9oZ37b M8KH9oZ37 98
    M8KH9oZ37y M8KH9oZ37 121
    M8KH9oZ37z M8KH9oZ37 122
    
    (12 row(s) affected)
    */
    


    If this is what you mean, you really need to talk about why you want to do this.  From where is this data coming?  How are you planning on using this?

    Tuesday, September 27, 2011 5:48 PM
    Moderator
  • The sample showed only the last letter ordered by ASCII (everything else was the same). However, if we need to order on each letter and our column's length is fixed 10 chars, then we may need to write substring for each of the 10 chars or use binary collation in order by clause.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, September 27, 2011 5:54 PM
    Moderator
  • The sample showed only the last letter ordered by ASCII (everything else was the same). However, if we need to order on each letter and our column's length is fixed 10 chars, then we may need to write substring for each of the 10 chars or use binary collation in order by clause.


    I also suggest that if you need this based on every character position that the collation needs to be a binary collation.  It is just not so clear to me what is going on.
    Tuesday, September 27, 2011 6:13 PM
    Moderator
  • In my sample I just gave an example of last letter. But YES I am expecting a order on each letter.

     

    Example 

    default SQL order with Latin1_General_CS_AS selects

    M8QGm4a112

    M8QGm4c134

    M8QGm4W198

    M8QGm4Y112

     

    and I am expecting in ASCII order as follows

     

    M8QGm4W198

    M8QGm4Y112

    M8QGm4a112

    M8QGm4c134

     


    ________________________________________ Mohammed Abdul Basit .NET Developer ________________________________________
    Tuesday, September 27, 2011 7:43 PM
  • Thanks for your response.

     

    The "weird_Key" is generated by application based on the current time. And what I was trying is do is while retrieving back from database I was hoping that there is a simple way( that may be I was overlooking) to get in the order of ASCII (so that I know the order in which the key or was generated).

    I do have an alternate way at client side in C# to sort two strings in ascii that I can use
            public int Compare(string key1, string key2)
            {
               return string.CompareOrdinal(key1, key2);
            }


    ________________________________________ Mohammed Abdul Basit .NET Developer ________________________________________
    Tuesday, September 27, 2011 7:52 PM
  • Try

    declare @t table (Col varchar(100))
    insert into @t 
    select
    'M8QGm4a112'
    union all select
    'M8QGm4c134'
    union all select
    'M8QGm4W198'
    union all select
    'M8QGm4Y112'
    
    select * from @t order by Col -- regular 
    
    select * from @t ORDER BY Col COLLATE SQL_Latin1_General_Cp437_BIN -- binary (ASCII) collation
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, September 27, 2011 7:57 PM
    Moderator
  • I basically recommend that you orient what you are doing according to Naomi's post; however, I still think that you should aim at defining the column explicitly to collate SQL_Latin1_General_Cp437_BIN.  Moreover, if you really are going to use this column as either the primary key or a unique key then you MUST designate this collation.

    Tuesday, September 27, 2011 8:09 PM
    Moderator

  • Try

     

    declare @t table (Col varchar(100))
    insert into @t 
    select
    'M8QGm4a112'
    union all select
    'M8QGm4c134'
    union all select
    'M8QGm4W198'
    union all select
    'M8QGm4Y112'
    
    select * from @t order by Col -- regular 
    
    select * from @t ORDER BY Col COLLATE SQL_Latin1_General_Cp437_BIN -- binary (ASCII) collation
    


     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    It worked :)

    Is there any good reference to know all the collates with details for SQL server?  

    http://msdn.microsoft.com/en-us/library/ms180175.aspx just lists out the available collations but with no details 


    ________________________________________ Mohammed Abdul Basit .NET Developer ________________________________________
    Tuesday, September 27, 2011 8:15 PM
  • Yes, I had to go through several links before I figured the right binary collection to use. Something MS makes it extremely difficult to find the right info in one place, you have to look though many links and they still don't have an answer.

    Try googling and see if not MS sites will show up with better and more concise explanations.

    BTW, I think I figured out the right collations after finally landing at this link

    http://msdn.microsoft.com/en-us/library/aa176551(SQL.80).aspx


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, September 27, 2011 8:24 PM
    Moderator