CODEWITHSUNDEEP
sql-serversql-order-by
Bishan
Bishan

Reputation: 15710

T-SQL ORDER BY varchar field

Here is my current query..

SELECT invNumber FROM invs ORDER BY invNumber DESC

..and result is:

1032-IN1
1032
1031-IN1
1031
1030-IN4
1030-IN3
1030-IN2
1030-IN1
1030
1029

Expected result is:

1032
1032-IN1
1031
1031-IN1
1030
1030-IN1
1030-IN2
1030-IN3
1030-IN4
1029

How can I achieve this?

Upvotes: 0

Views: 59

Answers (3)

Ed Bangga
Ed Bangga

Reputation: 13006

Considering different datatypes, this solution will help.

select [invNumber] from invs 
order by left([invNumber], (case when charindex([invNumber], '-') > 1 then charindex([invNumber], '-')-1 else len([invNumber]) end)) desc, 
        right([invNumber], case when charindex([invNumber], '-') > 1 then len([invNumber]) - charindex([invNumber], '-') else
               len([invNumber]) end) asc

Upvotes: 0

Thom A
Thom A

Reputation: 95561

As i mentioned, it sounds like you need to normalise your data. The reason you're getting the sort order you have is because you're expecting strings to sort in the same order as a numerical data type; where that simply isn't true. A value like '2' is greater than '10' with strings.

I would split the values into 3 parts, and CAST/CONVERT them before ordering. There is likely a better way here, but it's the way I got to the logic in the end:

SELECT YT.YourColumn
FROM (VALUES('1032-IN1'),
            ('1032'),
            ('1031-IN1'),
            ('1031'),
            ('1030-IN4'),
            ('1030-IN3'),
            ('1030-IN2'),
            ('1030-IN1'),
            ('1030'),
            ('1029'))YT(YourColumn)
    CROSS APPLY (VALUES(NULLIF(CHARINDEX('-',YT.YourColumn),0)))CI(h)
    CROSS APPLY (VALUES(TRY_CONVERT(int,LEFT(YT.YourColumn,ISNULL(CI.h-1,LEN(YT.YourColumn)))),STUFF(YT.YourColumn,1,CI.h,'')))V(Incremental,RightPart)
    CROSS APPLY (VALUES(LEFT(V.RightPart,PATINDEX('%[0-9]%',V.Rightpart)-1),TRY_CONVERT(int,STUFF(V.RightPart,1,PATINDEX('%[0-9]%',V.Rightpart)-1,''))))R([In],InNo)
ORDER BY V.Incremental DESC, R.[In], R.InNo DESC;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

We can try a two-tiered sort here:

SELECT invNumber
FROM invs
ORDER BY
    CASE WHEN invNumber LIKE '%-%'
         THEN CAST(SUBSTRING(invNumber, 1, CHARINDEX('-', invNumber)-1) AS INT)
         ELSE CAST(invNumber AS INT) END DESC,
    invNumber;

The first level of sorting checks if the invoice number contains a dash component. If it does, then we substring off the number only and cast to integer. Otherwise, if the invoice number be a pure number, we just cast right away. The second level of sorting uses invNumber directly, to break the tie, should one occur.

Upvotes: 3

Related Questions