Another Interesting Sort

Categories: News, Professional, SSC, SSSOLV
Tags: ,
Comments: 1 Comment
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.

[codesyntax lang=”tsql”]

[/codesyntax]

Solution

[codesyntax lang=”tsql”]

[/codesyntax]

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.

1 Comment - 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..

Leave a comment

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 Sunday, May 19, 2019