Order by numbers desc then by letters asc-CodePudding

I have a varchar column and the current order is like this:

select name from table order by name

1value
2value
3value
Avalue
Bvalue
Cvalue

I want to order first by numbers desc and then by letters asc:

3value
2value
1value
Avalue
Bvalue
Cvalue

How can I do this?

CodePudding user response：

You can use TRY_CAST and LEFT to achieve this.

SELECT * FROM
   #p
ORDER BY
-- Numeric first
TRY_CAST(LEFT(your_value,1) AS int) DESC,
-- If numeric, sort with a DESC
CASE WHEN
    TRY_CAST(LEFT(your_value,1) AS int) IS NOT NULL
THEN
    your_value
END DESC,
-- Otherwise, sort with an ASC
CASE WHEN
    TRY_CAST(LEFT(your_value,1) AS int) IS NULL
THEN
    your_value
END ASC;