CODEWITHSUNDEEP
sqlsql-servert-sqlsorting
guaike
guaike

Reputation: 2491

Help with T-sql special sorting rules

I have a field like：

SELECT * FROM 
(
    SELECT 'A9t' AS sortField UNION ALL 
    SELECT 'A10t' UNION ALL 
    SELECT 'A11t' UNION ALL 
    SELECT 'AB9F' UNION ALL 
    SELECT 'AB10t' UNION ALL        
    SELECT 'AB11t'  
) t ORDER BY sortField

and the result is:

sortField
---------
A10t
A11t
A9t
AB10t
AB11t
AB9F

Actually I need is to combine the string and number sorting rules:

sortField
---------
A9t
A10t
A11t
AB9F
AB10t
AB11t

Upvotes: 1

Views: 625

Answers (3)

Tomas Jansson
Tomas Jansson

Reputation: 23472

I would say that you have combined the alpha and numeric sort. But what I think you're asking is that you want to sort letters in ascending order and numbers in descending order, and that might be hard to do in a nice looking way. The previous answers will not working for your problem, the problem is that Martin Smith's solution doesn't take strings with two letters as prefix and Parkyprg doesn't sort numbers before letters as you ask for.

What you need to do is to use a custom order, see example here: http://www.emadibrahim.com/2007/05/25/custom-sort-order-in-a-sql-statement/, but that is a tedious way to do it.

EDIT: Martins Smith's solution is updated and works just fine!

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453308

SELECT   *
FROM     ( 
            SELECT 'A9t' AS sortField UNION ALL 
            SELECT 'A10t' UNION ALL 
            SELECT 'A11t' UNION ALL 
            SELECT 'AB9F' UNION ALL 
            SELECT 'AB10t' UNION ALL        
            SELECT 'AB11t' 
         )
         t
ORDER BY LEFT(sortField,PATINDEX('%[0-9]%',sortField)-1)                                                                                       ,
         CAST(substring(sortField,PATINDEX('%[0-9]%',sortField),1 + PATINDEX('%[0-9][A-Z]%',sortField) -PATINDEX('%[0-9]%',sortField) ) AS INT),
         substring(sortField,PATINDEX('%[0-9][A-Z]%',sortField)   + 1,LEN(sortField))

Upvotes: 4

CristiC
CristiC

Reputation: 22698

If the first character is always a letter, try:

SELECT * FROM 
(
    SELECT 'A9t' AS sortField UNION ALL 
    SELECT 'A10t' UNION ALL 
    SELECT 'A11t'
) t ORDER BY substring(sortField,2,len(sortField)-1) desc

Upvotes: 1

Related Questions