Re: order by

From: Pablo Sanchez <pablo_at_dev.null>
Date: Tue, 16 Apr 2002 09:33:18 -0600
Message-ID: <3vXu8.12$x_3.71867@news.uswest.net>

"XelA" <asilic_at_net.hr> wrote in message
news:a9gsen$kcu$1_at_sunce.iskon.hr...
> <meurer_at_swms.de> wrote in message

news:a9gs6j$gju$02$1_at_news.t-online.com...
>
> Hello,
>
> I have an ordering problem. I wish to order the strings in the table
> 'tab_test' as an incremented statement. But the problem is that
oracle can't
> recognize that the number '10' as a number.

It is treating it as a string and sorting it by ASCII code.

To efficiently solve this problem, you can do one of several things:

1. Break the data up in the column between alpha and numeric data and perform your ORDER BY accordingly
2. Build a function based index on the data.
3. Very inefficient and data dependent:

SQL> select * from fiz order by col_1;

COL_1

---

A1
A10
A2
A9

SQL> select * from fiz order by to_number(substr(col_1,2));

COL_1

---

A1
A2
A9
A10

SQL>

--
Pablo Sanchez, High-Performance Database Engineering
mailto:pablo_at_hpdbe.com
Available for short-term and long-term contracts

Received on Tue Apr 16 2002 - 10:33:18 CDT