An Interesting Sort

Tags: ,
Comments: 8 Comments
Published on: October 12, 2011

I just came across a pretty peculiar sort requirement.  The requirement made me sit and think a bit.  Since it was somewhat peculiar, I decided I would share the solution.

So, let’s start with a little sample data, and then I can go over the requirements.

[codesyntax lang="tsql"]

[/codesyntax]

Now, we only really have one field that is sortable in this dataset.  And as the title of this post alludes, the sort of that field is not straight forward.  For this data, we need to have the results sorted alpha first and then numeric.

I looked at this and thought, that should be fixed (based on the data) by simply adding a ‘DESC’ to the order by.  Oh but not, that is not entirely accurate.  More test data was added to the sample set with more requirements.  So let’s expand the data set first.

[codesyntax lang="tsql"]

[/codesyntax]

With this expanded data, it becomes obvious that a simple ‘DESC’ will not fix the issue.  That would place anything the XYZ entry at the top of the list.  But wait, take a look at the second Alpha sequence in the strings.  That complicates things a tiny bit more.  That second alpha sequence also has to be sorted ahead of anything that is numeric.  To further complicate it – it must be in ASC order alpha then numeric as well.

TaDa

So, with a little testing and a nifty trick I was able to come up with something that works.  Let’s take a look at it.

[codesyntax lang="tsql"]

[/codesyntax]

You will see that I have three conditions in my Order By clause.  Two of those contain a case statement.  By checking to see if something is numeric, I can make sure alpha is placed before numeric.  By including the middle condition, I was able to ensure the correct order for the first alpha sequence.  Without this middle condition, the Alpha strings were all returned before the numeric, but the Alpha was not ordered properly.

Recap

Despite some really odd strings to be ordered and out of the ordinary sorting requirements, it is possible with a little thinking.  My biggest friend here in this requirement was the use of the case statement.  Using the CASE really helped to simplify what I needed to achieve.

8 Comments - Leave a comment
  1. Jack Corbett says:

    Great post. Using CASE in ORDER BY and WHERE clauses can add a lot of power and flexibility. I don’t think I’d have come up with the solution though.

  2. WayneS says:

    Interesting. I have to ask if this odd sort goes back to a bad design, where this column should be separated into separate columns? (Just can’t tell from the test data.) Putting this into separate columns, with a computed column for combining them, would have simplified the sort.

    • Jason Brimhall says:

      Thanks Wayne. I think it was a really bad design. I’d much rather see the actual data broken up into separate columns.

  3. Jason Brimhall says:

    Well, as it would turn out – the requirements for this sort became vastly different from what explained the first two times. Any occurrence of alpha in any position should be sorted by Alpha first and then numeric. YUCK.

    It looks like the best solution is to use a collation
    COLLATE SQL_EBCDIC037_CP1_CS_AS

  4. Jack Corbett says:

    I was wondering if there would be more REQs that screw up the way that worked.

    Yes the good old EBCDIC. That used to screw us when transferring data from DB2400 to SQL Server.

    • Jason Brimhall says:

      Yeah new reqs :( I haven’t used EBCDIC in SQL so didn’t think of it. I’m angling for a redesign for this person that asked for the help.

Leave a comment

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">






Calendar
October 2011
M T W T F S S
« Sep   Nov »
 12
3456789
10111213141516
17181920212223
24252627282930
31  
Content
SQLHelp

SQLHelp


Welcome , today is Friday, August 29, 2014