Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

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);

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.

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);

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.

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

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.

Comments

Posted by Kevin on 13 October 2011

Hi Jason,

Few cases that you will need to test against.

You will need to add a final order by merchantID to the order by to sort the last half of the merchantID. Meaning you are not guaranteed an order between ABC4567892345678 and ABC4567812345678. This is true of non-numeric, ABC45678ABC45678 and ABC45678ZXY4567, will order differently depending on table order.

Try to simplify CASE WHEN ISNUMERIC(REVERSE(RIGHT(REVERSE(RIGHT(merchantid,LEN(MerchantID)-8)),3))) = 1 THEN 1 ELSE 0 END I think it can be CASE WHEN ISNUMERIC(LEFT(RIGHT(merchantid,LEN(MerchantID)-8),3)) = 1 THEN 1 ELSE 0 END. RIGHT(REVERSE()) is the same as a LEFT.

Also depending on your data ISNUMERIC may not always work. ISNUMERIC('1D2') returns a 1 also.

-Kevin

Leave a Comment

Please register or log in to leave a comment.