Solved

Order version numbers in Oracle.

Posted on 2013-05-13
6
506 Views
Last Modified: 2013-05-14
In Oracle SQL how can I sort by version numbers, at the moment version numbers are as follows when I do a simple order by of the version column...

6.0.1
6.0.10
6.0.11
6.0.12
6.0.13
6.0.14
6.0.2
6.0.3

The list should be as follows...

6.0.1
6.0.2
6.0.3
6.0.10
6.0.11
6.0.12
6.0.13
6.0.14
0
Comment
Question by:Blowfelt82
6 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
just do a conversion to a number and all should be fine

order by to_number(your_column)
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
you need to ensure that no characters etc are there in that version number column otherwise you will get an error saying invalid number when you use to_number(..) conversion.
0
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 500 total points
Comment Utility
- try using lpad() function if your column is not number datatype

- my example below pads the front of the data with spaces up to 10 characters as your data length is 6. you can increase the 10 if the data length in the version column is more than 10.

SELECT version FROM tablename
ORDER BY LPAD(version, 10);
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Closing Comment

by:Blowfelt82
Comment Utility
Worked like a charm - thanks
0
 
LVL 23

Expert Comment

by:OP_Zaharin
Comment Utility
glad able to help :)
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
:)

interesting question, I was thinking of regexp_ replace for this, but I hate regular expressions to be honest so I went looking and:
a different approach is recorded here
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now