ORDER BY; Letters Then Numbers

[mkingrey]

I have a recordset that I would like to sort letters to appear first then numbers to appear last. Is it possible to sort like the following?


A
B
C
R
2
3
4
7

 

[Juice05]

How about this? It works in MSSQL.

Select Field, 

Case IsNumeric(Field)
	When 1 Then
	'1' + Field
	Else
	'0' + Field
End D
From Table
Order By D

 

[JarlH]

And here's an ANSI/ISO compliant solution:

SELECT ...
FROM ...
WHERE ...
ORDER BY
  CASE WHEN SUBSTRING(field FROM 1 FOR 1) BETWEEN '0' AND '9' THEN 1
    ELSE 0 END,
  FIELD;

I.e. if first character is 0 - 9, place after all other rows.

(Or you can use a collation which sorts digits after letters, if available...)