How to Sort Semantic Versioning in Redshift

Possibly exotic, still: the aim of this page📝 is to explain how to correctly sort semantic version numbers in a table using SQL. We will use an example of sorting AMIs based on semantic versioning. I need the table as I keep getting questions about Ubuntu CVE on AMIs we are using. And I have this info surfaced in a Redshift instance.

1 min readJan 30, 2024

Semantic versioning is a versioning scheme that uses three numbers: major, minor, and patch.
However, sorting semantic version numbers as strings can lead to incorrect results. (e.g. 2.3.9 comes incorrectly before 2.3.25)
To correctly sort semantic version numbers, we need to split the version into its components and compare them numerically.
In PostgreSQL and AWS Redshift, we can use the SPLIT_PART function to split the version number.
We can then cast each component to an integer for numerical comparison.

SELECT ami_version, cve, severity
FROM my_cve_table
GROUP BY ami_version, cve, severity
ORDER BY 
  CAST(SPLIT_PART(ami_version, '.', 1) AS INTEGER) DESC,
  CAST(SPLIT_PART(ami_version, '.', 2) AS INTEGER) DESC,
  CAST(SPLIT_PART(ami_version, '.', 3) AS INTEGER) DESC;

Split the version number into its components using the SPLIT_PART function.
Cast each component to an integer for numerical comparison.
Sort the components in descending order to achieve the desired sorting.

How to Sort Semantic Versioning in Redshift

LINKS

Written by Pavol Kutaj