Try this:
SELECT FOO, f(FOO) as x from bar ORDER BY x;
"f()" is a fuction that returns a floating point value
as follows
f(1) = 1.00
f(10) = 10.00
f(1a) = 1.01
f(1b) = 1.02
--- Laurette Cisneros <laurette@nextbus.com> wrote:
>
> How to sort a text column in numerical order?
>
> I have a text column in a table:
>
> ...
> col2 text
> ...
>
> which can contains values that are both number and a combination of
> numeric
> and text:
>
> 1
> 1a
> 2
> 2a
> 2b
> 10
> 12
> 12a
> etc.
>
> Of course, they aren't entered into the table in any particular
> order.
>
> A select like this:
>
> select * from tablex order by col2;
>
> returns this:
>
> 10
> 12
> 12a
> 1
> 1a
> 2a
> 2b
>
> what we would like to get is:
> 1
> 1a
> 2
> 2a
> 2b
> 10
> 12
> 12a
>
> Any ideas on how to achieve this?
>
> Thanks,
>
> --
> Laurette Cisneros
> The Database Group
> (510) 420-3137
> NextBus Information Systems, Inc.
> www.nextbus.com
> ----------------------------------
> "Intelligence complicates. Wisdom simplifies."
> -- Mason Cooley
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
=====
Chris Albertson
Home: 310-376-1029 chrisalbertson90278@yahoo.com
Cell: 310-990-7550
Office: 310-336-5189 Christopher.J.Albertson@aero.org
__________________________________________________
Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
http://sbc.yahoo.com