Re: How to Numerically Order varchar2 Column

To_number (..), and Susbts(..) may work if and when there are just a few rows in the table (no need for indexes). However, if there are a lot of rows, the query could take too long to yoeld a result.
For the subject problem, there are two solutions (in case there are too much rows).
1.-Desnormalization
That is, adding a new column bearing a numeric data type so as to contain the result of a function representing the order position of the row. It could be calculated with the substr and to_number functions and recorded in the new column by updating it.
Something like:
update table_name
set new_column= to_number(substr(....))

2.-Auxiliar External Table
Creating a new table with numeric field containg the order position of the row and the identification of the row int the problem table. Then adding an index to both table.

Norberto Pontt

"Matt B." <mcb_at_ds.znet.com> escribió en el mensaje news:tcvv5622555hf3_at_corp.supernews.com...
> "Freedom" <luciver_at_teleweb.at> wrote in message
> news:mtMz6.106274$cj.4421144_at_news.chello.at...
> > I have a Table with following Data
> >
> > A10/1
> > A10/22
> > A10/3
> > A10/4
> > A1/10
> > A11/1
> > A1/11
> > A11/2
> > A1/12
> > A11/3
> > A1/13
> > A11/4
> > A1/14
> > A11/5
> >
> > and I would like to sort this corectly like a number type
> > A1/9
> > A1/8
> > A1/7
> > A1/6
> > A1/5
> > A1/4
> > A1/3
> > A1/2
> > A1/1
> > B1/9
> > B1/8
> > and so on
> > Can anyone help me?
>
> SELECT my_column
> FROM my_table
> ORDER BY
> SUBSTR(my_column,1,1), -- A,B,C,D, etc.
> TO_NUMBER(my_column,2,1) -- 1/9, 1/8, 1/7, etc.
>
> -Matt
>
>
Received on Sun Apr 08 2001 - 16:17:10 CEST