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     |
up vote 11 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!)

  • 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 '14 at 15:51
  • 2
    If you're dealing with Maven versions or versions that may contain non-numeric characters, you can remove the non-numeric characters first: string_to_array(regexp_replace(version, '[^0-9.]', '', 'g'), '.')::int[] – Samuel Aug 23 '16 at 10:34
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)

  • Do you need to convert these to numbers? Otherwise I would expect 10 to be between 1 and 2. – JNK Aug 18 '14 at 18:34
  • This is confirmed by your fiddle... – JNK Aug 18 '14 at 18:40
  • Deleting mine in favor of this. string_to_array is much simpler than regex. – Chris Betti Aug 18 '14 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 '14 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. – Sun Aug 18 '14 at 21:53

create extension semver;

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

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

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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