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

create table #tempSort
(
        MerchantID      varchar(16),    
        POS             varchar(2),
        Amount          int
)
INSERT into #tempSort
VALUES ('ABC4567812345678', Null, Null);
INSERT into #tempSort
VALUES ('ABC45678ABC45678', Null, Null);
INSERT into #tempSort
VALUES ('1234567812345678', Null, Null);
INSERT into #tempSort
VALUES ('12345678ABC45678', Null, Null);

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

create table #tempSort
(
        MerchantID      varchar(16),    
        POS             varchar(2),
        Amount          int
)
INSERT into #tempSort
VALUES ('ABC4567812345678', Null, Null);
INSERT into #tempSort
VALUES ('ABC45678ABC45678', Null, Null);
INSERT into #tempSort
VALUES ('1234567812345678', Null, Null);
INSERT into #tempSort
VALUES ('12345678ABC45678', Null, Null);
INSERT into #tempSort
VALUES ('ABC45678ZXY4567', Null, Null);
INSERT into #tempSort
VALUES ('XYZ45678ZXY4567', Null, Null);

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

SELECT MerchantID
from #tempSort
ORDER BY CASE WHEN ISNUMERIC(LEFT(merchantID,3)) = 1 THEN 1 ELSE 0 END ASC
,LEFT(merchantID,8) ASC
,CASE WHEN ISNUMERIC(REVERSE(right(REVERSE(right(merchantid,LEN(MerchantID)-8)),3))) = 1 THEN 1 ELSE 0 END

[/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.