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

·

Follow

1 min read

·

Jan 30, 2024

--

Listen

Share

• 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

• https://docs.aws.amazon.com/redshift/latest/dg/SPLIT_PART.html
• https://semver.org/
• https://ubuntu.com/security/cves