An Interesting Sort

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”]


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”]


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.


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”]


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.


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.

7 thoughts on “An Interesting Sort”

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

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

  2. 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

  3. 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.

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