Home » SQL & PL/SQL » SQL & PL/SQL » ORDER BY
ORDER BY [message #260682] Mon, 20 August 2007 12:45 Go to next message
bella13
Messages: 90
Registered: July 2005
Member
When I order by a particular column, this is the order the data is displayed in

52-09
52-09.1
52-09.11
52-09.12
52-09.13
52-09.2
52-09.20
52-09.21
52-09.22
52-09.3
52-09.31
52-09.4

Is there any way i can order it as follows

52-09
52-09.1
52-09.2
52-09.3
52-09.4
52-09.11
52-09.12
52-09.13
52-09.20
52-09.21
52-09.22
52-09.31

thanks for your time and help
Re: ORDER BY [message #260683 is a reply to message #260682] Mon, 20 August 2007 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
order by substr(col,1,5), to_number(substr(col,7)

Regards
Michel
Re: ORDER BY [message #260686 is a reply to message #260683] Mon, 20 August 2007 13:00 Go to previous messageGo to next message
bella13
Messages: 90
Registered: July 2005
Member
Great! It worked. Thanks...
Re: ORDER BY [message #260733 is a reply to message #260682] Mon, 20 August 2007 16:03 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Will the first part of the column ever get bigger then 5? for example could you ever have 100-129.3

If you will, try the following.

order by to_number(substr(col,1,instr(col,'.')-1)),
         to_number(substr(col,instr(col,'-')+1,instr(col,'.') - instr(col,'-')-1)),
         to_number(substr(col,instr(col,'.')+1))
Previous Topic: password encryption
Next Topic: PLS-00201
Goto Forum:
  


Current Time: Sat Dec 10 07:18:23 CST 2016

Total time taken to generate the page: 0.08763 seconds