Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Given a "SoftwareReleases" table:

| id | version |
|  1 | 0.9     |
|  2 | 1.0     |
|  3 | 0.9.1   |
|  4 | 1.1     |
|  5 | 0.9.9   |
|  6 | 0.9.10  |

How do I produce this output?

| id | version |
|  1 | 0.9     |
|  3 | 0.9.1   |
|  5 | 0.9.9   |
|  6 | 0.9.10  |
|  2 | 1.0     |
|  4 | 1.1     |
share|improve this question

3 Answers 3

up vote 3 down vote accepted

To produce your desired output, you can simply:

SELECT id, version
FROM   versions
ORDER  BY string_to_array(version, '.')::int[];

One can cast a whole text array to an integer array (to sort 9 before 10).
One can ORDER BY array types. This is the same as ordering by each of the elements.

SQL Fiddle (reusing @a_horse's fiddle, thanks!)

share|improve this answer
1  
This is great. Somehow, this sorts missing values correctly, without having to specify the order of nulls: (1.6.9 -> 1.7 -> 1.7.1), rather than (1.6.9 -> 1.7.1 -> 1.7). Accepting this one. –  Chris Betti Aug 21 at 15:51
select id,
       name, 
       v[1] as major_version,
       v[2] as minor_version,
       v[3] as patch_level
from (
   select id, 
          name, 
          string_to_array(version, '.') as v
   from versions
) t
order by v[1]::int desc, v[2]::int desc, v[3]::int desc;

SQLFiddle: http://sqlfiddle.com/#!15/c9acb/1

If you expect more elements in the version string, just use more array indexes. If the index does not exist, the result will be null (e.g. v[10] will return null)

share|improve this answer
    
Do you need to convert these to numbers? Otherwise I would expect 10 to be between 1 and 2. –  JNK Aug 18 at 18:34
    
This is confirmed by your fiddle... –  JNK Aug 18 at 18:40
    
Deleting mine in favor of this. string_to_array is much simpler than regex. –  Chris Betti Aug 18 at 18:42
    
@JNK: that is what the v[1]::int is about. It casts the string to an integer. –  a_horse_with_no_name Aug 18 at 19:10
    
The only change I'd make to your SQL is the order by. I suggest taking out desc and that will create the result set @Chris Betti is looking for. –  sunk818 Aug 18 at 21:53

create extension semver;

select id, version from SoftwareReleases order by version::semver;

http://www.pgxn.org/dist/semver/doc/semver.html

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.