Another option would be :
SELECT
    Field1
    , Field2
    , Field3
FROM
    Table1
ORDER BY
	case
	when   ISNUMERIC(SortField) then
	    CAST(SortField AS Int) 
	else null  --(or 0)
	end DESC
	

Brian Freeman
(xxx) xxx-xxxx  ext. 415
Carnegie Technologies/Bluewave Computing 
www.carnegie.com and www.bluewave-computing.com

-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Tuesday, January 28, 2003 6:43 PM
To: sql language
Subject: [sql_language] Re: Ordering a varchar field numerically


Possibly something like:

SELECT
    Field1
    , Field2
    , Field3
FROM
    Table1
WHERE
    ISNUMERIC(SortField)
ORDER BY
    CAST(SortField AS Int) DESC
UNION
SELECT
    Field1
    , Field2
    , Field3
FROM
    Table1
WHERE
    NOT ISNUMERIC(SortField)
ORDER BY
    SortField DESC

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Simon Hurr" <simon@f...>
Subject: [sql_language] Ordering a varchar field numerically


: Hi there,
:
: We have found another problem where Access lets you get away with
: something that SQL doesn't seem to...
:
: We have a column in one table which is varchar(8) - it usually contains
: line numbers but some lines are marked with letters(!)
:
: For example, for one parent record we have sixteen line records - line
: numbers 1 to 15 and the sixteenth line being line number 'TAX'.
:
: Access allows us to do the following:
: SELECT field1
: FROM table1
: ORDER BY Val(table1.field1)>0 DESC, Val(table1.field1) ASC
:
: This has the effect of putting all numeric fields before all non-numeric
: fields, then sorting the numeric ones into numeric sequence.
:
: Thus, we would have lines 1 to 15 in numeric sequence, then line TAX at
: the end.
:
: Is there a relatively straightforward way to accomplish this sort of thing
: in T-SQL ?
:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~