CODEWITHSUNDEEP
sqlgoogle-bigquery
sql runner
sql runner

Reputation: 25

Set variable with an array element

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

Upvotes: 1

Views: 140

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

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

enter image description here

As alternative you can use below variation of final select statement

select version
from test
order by normaizedsemanticversion(version) desc 
limit 1

Upvotes: 1

Related Questions