I have a table with apps versions (v1.1.1, v1.1.2, v1.10.1, etc.).
Using REGEXP_EXTRACT_ALL, I have an arrays with the numbers as elements.
Any idea why I can't set the max of each element to a variable?
This is the code I use:
DECLARE x DEFAULT 0;
SET x = (
SELECT
max(REGEXP_EXTRACT_ALL(app_version, "\\d+")[SAFE_ORDINAL(2)])
FROM
'table_with_app_version');
Thanks
Below is for BigQuery Standard SQL
#standardsql
create temp function normaizedsemanticversion(semanticversion string)
as ((
select string_agg(
if(isdigit, repeat('0', 8 - length(chars)) || chars, chars), '' order by grp
) || '..zzzzzzzzzzzzzz'
from (
select grp, isdigit, string_agg(char, '' order by offset) chars,
from (
select offset, char, isdigit,
countif(not isdigit) over(order by offset) as grp
from unnest(split(semanticversion, '')) as char with offset,
unnest([char in ('1','2','3','4','5','6','7','8','9','0')]) isdigit
)
group by grp, isdigit
)));
create temp function comparesemanticversions(
normsemanticversion1 string,
normsemanticversion2 string)
as ((
select
case
when v1 < v2 then 'v2 newer than v1'
when v1 > v2 then 'v1 newer than v2'
else 'same versions'
end
from unnest([struct(
normaizedsemanticversion(normsemanticversion1) as v1,
normaizedsemanticversion(normsemanticversion2) as v2
)])
));
with test as (
select 'v1.0.0-alpha' version union all
select 'v1.0.0-alpha.1' union all
select 'v1.0.0-alpha.beta' union all
select 'v1.0.0-beta' union all
select 'v1.0.0-beta.2' union all
select 'v1.0.0-beta.11' union all
select 'v1.0.0-rc.1' union all
select 'v1.0.0' union all
select 'v1.1.1' union all
select 'v1.1.2' union all
select 'v1.10.1'
)
select string_agg(version order by normaizedsemanticversion(version) desc limit 1)
from test
with output
As alternative you can use below variation of final select statement
select version
from test
order by normaizedsemanticversion(version) desc
limit 1