Sort by string values

Hello! I am using AWS Managed Grafana and I trying to sort results from an Athena query. The bar chart shows downloaded versions for an app. Version examples: 4.26.0, 4.7.0, 4.25.1.

The query looks like:

SELECT count(REGEXP_EXTRACT(key, '[0-9]+.[0-9]+.[0-9]+', 0)) as downloads, REGEXP_EXTRACT(key, '[0-9]+.[0-9]+.[0-9]+', 0) AS version FROM "s3_repo_access_logs_db"."bucket_logs" WHERE operation like '%GET.OBJECT%' AND key LIKE '%.deb%' AND $__timeFilter(requestdatetime,'dd/MMM/YYYY:HH:mm:ss Z') group by REGEXP_EXTRACT(key, '[0-9]+.[0-9]+.[0-9]+', 0) order by REGEXP_EXTRACT(key, '[0-9]+.[0-9]+.[0-9]+', 0)

And this is how it is represented:

The thing is that 4.27.0 should be greater than 4.7.0.

4.7.0 should be the first in the list but it seems it is computed as string and the order is not ok. Is there any transformation or workaround I can apply to achieve it?

when it comes to software versioning 4.7 is lower than 4.27

but in terms of numeracy 4.7 is bigger than 4.27

I was able to solve it with:

WITH version_components AS (
    CAST(REGEXP_EXTRACT(key, '([0-9]+)\.([0-9]+)\.([0-9]+)', 1) AS INTEGER) AS major,
    CAST(REGEXP_EXTRACT(key, '([0-9]+)\.([0-9]+)\.([0-9]+)', 2) AS INTEGER) AS minor,
    CAST(REGEXP_EXTRACT(key, '([0-9]+)\.([0-9]+)\.([0-9]+)', 3) AS INTEGER) AS patch,
  FROM "s3_repo_access_logs_db"."bucket_logs"
  WHERE operation LIKE '%GET.OBJECT%' 
    AND key LIKE '%.deb%' 
    AND $__timeFilter(requestdatetime,'dd/MMM/YYYY:HH:mm:ss Z') 
  count(1) AS downloads,
  CAST(major AS VARCHAR) || '.' || CAST(minor AS VARCHAR) || '.' || CAST(patch AS VARCHAR) AS version
FROM version_components
GROUP BY major, minor, patch
ORDER BY major, minor, patch;

Until one day they add v to v4.27.0

Since that column is of type char, or varchar anything can happen

Proceed with caution when it comes with parsing

Try_cast type of function would be more resilient/fail safe in this case