20

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     |
32

To produce your desired output:

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

Cast the whole text array to an integer array (to sort 9 before 10) and ORDER BY that.
This is the same as ordering by each of the elements.
Shorter arrays come before longer ones with an equivalent leading part.

db<>fiddle here
Old sqlfiddle

3
  • 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. Aug 21 '14 at 15:51
  • 3
    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
  • 1
    I use this to find the max version and it works great SELECT max(string_to_array(build_version, '.')::int[] Sep 4 '19 at 20:42
6
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)

5
  • 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. Aug 18 '14 at 18:42
  • @JNK: that is what the v[1]::int is about. It casts the string to an integer. 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
5

create extension semver;

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

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

1
  • This is the correct answer as the selected one doesn't cope well with full semver like alpha release etc...
    – nolazybits
    Feb 2 at 22:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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