28

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     |
Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
Chris Betti
  • 477
  • 1
  • 5
  • 11

3 Answers3

38

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

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • 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
  • 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[] – Joviano Dias Sep 04 '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)

  • 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. –  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

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