(MySQL) To get the latest version based on the column which contains alphabet and number-CodePudding

I try to find the latest version based on the column :version, which contains alphabet and number. Here's the data format:

file_name             version    category
file_aaa_X01.csv       X01         aaa
file_aaa_X02.csv       X02         aaa 

file_bbb_X01.csv       X01         bbb
file_bbb_X02.csv       X02         bbb
file_bbb_X03.csv       X03         bbb
file_bbb_XY1.csv       XY1         bbb

file_ccc_X01.csv       X01         ccc
file_ccc_XY1.csv       XY1         ccc
file_ccc_XY2.csv       XY2         ccc
file_ccc_XY11.csv      XY11        ccc

So, for each category, the versions start with "XY" are always newer than the ones start with "X". The larger number in version it contains, the newer version it is.

The expected result will be

file_aaa_X02.csv       X02         aaa 
file_bbb_XY1.csv       XY1         bbb
file_ccc_XY11.csv      XY11        ccc

I tried to use order by version desc limit 1 and group by to get the order followed by alphabet and number in the descending way. However, for the case of 'XY2' and 'XY11', I cant get the version of 'XY11' as the answer. Thanks

CodePudding user response：

If you are using MySQL 8 , then REGEXP_SUBSTR along with ROW_NUMBER is one approach:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (
                  PARTITION BY category
                  ORDER BY REGEXP_SUBSTR(version, '^[A-Z] ') DESC,
                           CAST(REGEXP_SUBSTR(version, '[0-9] $') AS UNSIGNED) DESC
              ) rn
    FROM yourTable
)

SELECT file_name, version, category
FROM cte
WHERE rn = 1;