OraFAQ Forum: Forms » order by clause

Home » Developer & Programmer » Forms » order by clause (Oracle 8i, Windows xp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

order by clause [message #315270]

Sun, 20 April 2008 13:05

gargi_12300
Messages: 11
Registered: March 2008

Junior Member

Helloo
I need some help
actually have a table having attribute Citizen_id as primary key.
My citizen_id is of the form D001B001CTZ1,D001B001CTZ2,D001B001CTZ3,etc.
Now i want to arrange them in ascending order using order by clause.
It works fine until D001B001CTZ10 is reached.
AFter that it arranges as:
D001B001CTZ1
D001B001CTZ10
D001B001CTZ2
and so on...
please can suggest me how to prevent this and arrange to ids as D001B001CTZ1,D001B001CTZ2,.....D001B001CTZ10.

Report message to a moderator

Re: order by clause [message #315285 is a reply to message #315270]

Sun, 20 April 2008 15:47

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Ordering has been done correctly (as you are ordering CHARACTERS). You want to sort characters PLUS numbers. One way to do that is to split the ID do it like this:

SQL> WITH TEST AS
  2    (SELECT 'D001B001CTZ1' id FROM dual
  3     UNION
  4     SELECT 'D001B001CTZ2' id FROM dual
  5     UNION
  6     SELECT 'D001B001CTZ10' id FROM dual
  7     UNION
  8     SELECT 'D001B001CTZ3' id FROM dual
  9    )
 10  SELECT id FROM TEST
 11  ORDER BY SUBSTR(id, 1, 11), TO_NUMBER(SUBSTR(id, 12, 2));

ID
-------------
D001B001CTZ1
D001B001CTZ2
D001B001CTZ3
D001B001CTZ10

SQL>

Report message to a moderator

Re: order by clause [message #315897 is a reply to message #315270]

Wed, 23 April 2008 02:52

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

I put to you that the design of 'Citizen_id' is wrong. Instead of one field, it should have been stored in the database as four fields. You do NOT have to store data the same way that it appears on the screen or in a report.

David

Report message to a moderator

Re: order by clause [message #315986 is a reply to message #315270]

Wed, 23 April 2008 07:24

deepakkrjain
Messages: 4
Registered: April 2007
Location: Delhi

Junior Member

hi used this

select substr('D001B001CTZ1',12) from dual;

or

select substr(<field_name>,12) from <table_name>
order by <field_name>;

after that u goted the last digit in accending order.

Please try it.

Regards.
Deepak

Report message to a moderator