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.

Pavol Kutaj
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.

LINKS

--

--

Pavol Kutaj

Today I Learnt | Infrastructure Support Engineer at snowplow.io with a passion for cloud infrastructure/terraform/python/docs. More at https://pavol.kutaj.com