|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
June 2nd, 2009, 07:11 PM
|
|||
|
|||
|
ORDER BY using text as numeric
I have an SQL query (Oracle) that joins two fields into one (c1.step_number || '.' || c1.task_number AS STEP). When I combine fields like this it appears that it is treated as text and when I try to ORDER BY this field, it orders as:
1.0, 10.0, 10.1, 2.0, 3.0... Where I'd like it to order numerically like: 1.0, 2.0, 3.0, 10.0, 10.1... When I try using "CAST as Integer" around the ORDER BY, things get even worse. Not sure what's up with that. Any ideas out there on how I can get this ordered properly? Thanks in advance, -ST Last edited by steeltrap : June 2nd, 2009 at 07:13 PM.
|
|
#2
June 2nd, 2009, 11:45 PM
|
||||
|
||||
|
-->Thread moved to SQL Development.
What do you mean "it gets worse"? Show the ORDER BY part of your query.
__________________
Come JOIN the party!!! Quote of the Month: Pretension: The downside of being better than everyone else is that people tend to assume you're pretentious. Questions to Ponder: You can be overwhelmed and underwhelmed, but why can't you be simply whelmed? iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm") copyright© 2008 sbenj69
|
|
#3
June 3rd, 2009, 12:57 PM
|
|||
|
|||
|
What happens when I use:
Code:
" ORDER BY Cast(1 as Integer) Asc" is the order just seems to be scrambled up. i.e. 9.9, 10.9, 10.15, 11.1, 9.1... It's even worse than when I just use "ORDER BY 1 Asc" by itself. Like I mentioned, this column is a really two column results joined into one seperated by a "." (c1.step_number || '.' || c1.task_number AS STEP). I just can't figure out how to get SQL to treat this as an integer value so it will order numerically. Thanks again for the assistance! -ST
|
|
#4
June 3rd, 2009, 01:03 PM
|
||||
|
||||
|
could you just try sorting by those fields themselves and not with them joined together?
Code:
ORDER BY c1.step_number ASC, c1.task_number ASC
|
|
#5
June 3rd, 2009, 01:11 PM
|
|||
|
|||
|
I didn't want to do that just because I'm not actually displaying the individual fields in my results, just the combined results.
I actually went a different route trying to cast the data type in the query as such: Code:
CAST(c1.step_number || '.' || c1.task_number AS DOUBLE PRECISION) AS STEP and this works nicely. I think changing the data type to double precision as opposed to Integer is probably what did it. I think this would still work using cast with the order by function as long as the data type is double precision. Thanks for the help! -ST Last edited by steeltrap : June 3rd, 2009 at 01:13 PM.
|
|
#6
October 14th, 2009, 02:19 AM
|
|||
|
|||
|
Re: ORDER BY using text as numeric
HI,
I hope that the below SQL query will be useful for you. Select Cast(step_number as varchar)+'.'+Cast(task_number as varchar)as Number from tablename order by step_number asc. Thanks & Regards Sakthimeenakshi.S
|
|
| Viewing: ASP Free Forums > Database > SQL Development > ORDER BY using text as numeric |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
