Thread: collating advice

collating advice

From
Laurette Cisneros
Date:
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


Re: collating advice

From
Chris Albertson
Date:
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