| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 10/29/2000 : 23:44:51
|
Rob writes "I have a commission_code table with 2 columns:
comm_code varchar(8)
amount smallmoney
This table populates a select on an asp page. Some of the comm_codes are numeric (1, 2, 23, 12, etc) and some are strings (L1, L2, S4, etc).
Is is possible to return the rows ordered by the numeric comm_codes followed by the strings in ONE sql statement?
I have tried 'select * from commission_code order by comm_code'. Using the data above, this returns 1, 12, 2, 23, L1, L2, S4. The numeric values are not in order. I cannot use convert() in the order by because it fails on the string values.
Currently I am using 2 sql statements with the isNumeric() function, and it gets the job done, but there has to be a better way.
Thanks!
Rob" |
|
|
graz
Chief SQLTeam Crack Dealer
USA
4128 Posts |
Posted - 10/29/2000 : 23:44:51
|
How about:
SELECT Com_code = right('00' + comm_code, 2), amount
From . . .
Order by 1
Does that do it for you? |
 |
|
|
rswoods
Starting Member
4 Posts |
Posted - 10/30/2000 : 11:29:24
|
quote:
How about:
SELECT Com_code = right('00' + comm_code, 2), amount
From . . .
Order by 1
Does that do it for you?
<p>
Yes, that did it, with one minor modification:<br>
sql = "select comm_orderby = right('00000000' + comm_code, 8), " & _
"comm_code, " & _
"comm_amount " & _
"from commission " & _
"where carrier ='" & sCarrier & "' " & _
"order by 1 "
Thanks!
|
|
|
|
paulmelba
Starting Member
8 Posts |
Posted - 05/15/2007 : 15:49:52
|
| SPAM DELETED |
Edited by - jsmith8858 on 05/15/2007 16:03:23 |
|
|
| |
Topic |
|
|
|
order by problem
