Another Interesting Sort

Some code requirements can be met through various means including the use of a different collation – which means a lot of testing.

In October of 2011, I shared an example of a peculiar set of sort requirements.  Today, I am going to share another similar set of requirements.  Based on prior experience, when I saw this request on a sort order, I was sure I could find a simpler solution.  Today, I will share the requirements and solution with you.


Given a set of characters, you must be able to sort according to the following:

  1. !
  2. $
  3. ?
  4. @
  5. ^
  6. {
  7. >
  8. ASCII values


To demonstrate the requirements and solution, let’s create a temp table and populate it with some values like those in the requirements.


Sometimes the simplest solution requires a lot of testing.  When I came across the requirements, I thought it could be solved via a collation.  The problem was that I did not know which collation.  I had to test a few collations to find the collation that would create the correct result set.  If you are interested in learning about other collations, you can read this article.

Update 2019-05-21: Please read the comments from Solomon.

2 thoughts on “Another Interesting Sort”

  1. Hi Jason. I realize that it’s been 7 years since you posted this, but do you remember the details enough to elaborate a little on the specific requirements? At the very least, can you please clarify the following:

    1) Are the characters noted in 1 – 8 the literal first 8 characters always, or do they just need to be in that order even if some characters sort before that group?

    2) Can there be any characters that sort between them, or do those 8 need to be together?

    3) What exactly does item 9 mean “ASCII values”? Does that mean that all other characters should sort after those 8 but according to their ASCII value? Related to question #1 above, what if the ASCII value would sort it ahead of item #1 (i.e. “!”)?

    The reason I’m asking is because while you did find a collation that sorts items 1-8 in the specified order: a) there are still characters that sort before them, b) there are characters that sort between them, and c) the rest of the characters don’t even come close to sorting according to their ASCII values (nor should they given that this isn’t a binary collation).

    In order to fully see what is going on, the test data needs to be all 256 characters. The following demonstrates this:

    USE [tempdb];

    CREATE TABLE dbo.CodePage1251
    [Char] CHAR(1) COLLATE SQL_Latin1_General_Cp1251_CS_AS NOT NULL

    ;WITH cte AS
    FROM master.sys.columns
    INSERT INTO dbo.CodePage1251 ([Value], [Char])
    SELECT [num], [num]
    FROM cte;

    SELECT ROW_NUMBER() OVER (ORDER BY cp.[Char] ASC) AS [Position],
    FROM dbo.CodePage1251 cp
    ORDER BY cp.[Char] ASC;

    Position Char Value
    42 ! 33
    43 ” 34

    45 $ 36

    56 ? 63
    57 @ 64

    61 ^ 94

    64 { 123

    80 > 62

    100 1 49

    109 a 97
    110 A 65
    111 b 98

    As you can see in the results in the comment block at the bottom, the ordering of the “required” characters is correct, but: there are 41 characters that sort before them; there are characters between most of them; and uppercase and lowercase versions of the same letter will sort together, which can never be in ASCII value order.

    Meaning: if the requirements are to be taken literally (i.e. items 1 – 8 are the first eight, and all others sort strictly according to their ASCII values), then you need to do the CASE statement method that you did in the earlier, related post.

    Take care,

    1. Thanks Solomon. This was a long time ago, but I believe the specific requirements were to sort those characters in that order without regard to any other characters that may be before or between them. As for the ASCII, I believe it was just alpha-numerics without any special characters. It was a very specific request.

      Thanks for the follow-up. You know collation better than anybody, and I appreciate your input on these kinds of topics.

Leave a Reply to Solomon Rutzky Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.