Another Interesting Sort

Categories: News, Professional, SSC, SSSOLV
Tags: ,
Comments: 2 Comments
Published on: March 19, 2012

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.

Requirements

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

Setup

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

Solution

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 Comments - Leave a comment
  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
    (
    [Value] TINYINT NOT NULL CONSTRAINT [PK_CodePage1251] PRIMARY KEY,
    [Char] CHAR(1) COLLATE SQL_Latin1_General_Cp1251_CS_AS NOT NULL
    );

    ;WITH cte AS
    (
    SELECT TOP (256) CONVERT(BINARY(1), (ROW_NUMBER() OVER (ORDER BY @@MICROSOFTVERSION) – 1)) AS [num]
    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],
    cp.[Char],
    cp.[Value]
    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,
    Solomon..

    • Jason Brimhall says:

      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.



Calendar
March 2012
M T W T F S S
« Feb   Apr »
 1234
567891011
12131415161718
19202122232425
262728293031  

Welcome , today is Wednesday, November 13, 2019