SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 order by problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 10/29/2000 :  23:44:51  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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
4137 Posts

Posted - 10/29/2000 :  23:44:51  Show Profile  Visit graz's Homepage  Reply with Quote
How about:

SELECT Com_code = right('00' + comm_code, 2), amount
From . . .
Order by 1

Does that do it for you?
Go to Top of Page

rswoods
Starting Member

4 Posts

Posted - 10/30/2000 :  11:29:24  Show Profile  Reply with Quote
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!


Go to Top of Page

paulmelba
Starting Member

8 Posts

Posted - 05/15/2007 :  15:49:52  Show Profile  Visit paulmelba's Homepage  Click to see paulmelba's MSN Messenger address  Reply with Quote
SPAM DELETED

Edited by - jsmith8858 on 05/15/2007 16:03:23
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000