CARDINAL ORDERING SQL

Hi guys

Is there any way that I can order a column in the order 1,2,3 instead of 1,11,12,2,3,30.  I am trying to write a script using SQL.
ernie_shahAsked:
Who is Participating?
 
enachemcConnect With a Mentor Commented:
order by to_number(column)
0
 
ernie_shahAuthor Commented:
Thanking you for any assistance.
0
 
Geert GruwezConnect With a Mentor Oracle dbaCommented:
use lpad format the number as char with left padding zero's

select lpad(column, 20, '0') sorter, ...
from table
order by sorter

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Ephraim WangoyaConnect With a Mentor Commented:

That happens because your column is varchar.
You have to use a numeric field or convert the column to numeric when running you query

select *
from table
order by cast(Yourcolumn as int)

0
 
systanCommented:
why that? you can write like;
select thefield1youwant, thefield2youwant, thefield3youwant, thefield4youwant FROM table
much faster.

Oh,
Hello ewangoya,
Nice seeing you.
0
 
RezaSadighCommented:
Hello my Friend,
You can convert your field to numeric format in order part like this:
Select Field1, Field2 -- or some fields that you need
From ATable -- assume ATable is your table
Order by Cast(Field3 as int) -- assume Field3 is your ordering field
0
 
ernie_shahAuthor Commented:
Thank you guys for the responses i will try the suggestions and let you know.......
0
 
Geert GruwezOracle dbaCommented:
hey, my comment works too !

if you left pad a column with 0's it's sorted as a left padded string with 0's
0
All Courses

From novice to tech pro — start learning today.