Bigquery SQL MAX() text value

Question

Thanks for taking the time to look at this.

How to get the MAX of a text value in column A. I would like to and another where clause to show only one of the "FY20-Q4M#" (MAX value only)

Current Table

YY-QQ_STATUS	Program
FY20-Q2_ACTUALS	XYZ
FY20-Q3_ACTUALS	XYZ
FY20-Q3_BUDGET	XYZ
FY20-Q4M0	XYZ
FY20-Q4M1	XYZ
FY20-Q4M2	XYZ
FY20-Q4_BUDGET	XYZ
FY20-Q4_OUTLOOK	XYZ

question from:https://stackoverflow.com/questions/65832488/bigquery-sql-max-text-value

Answer 1

Try below

select 
    ifnull(format('FY%s-Q%sM%s', any_value(year), any_value(quartal), max(month)), any_value(YY_QQ_STATUS)) as YY_QQ_STATUS, 
    Program
from (
    select *, 
        regexp_extract(YY_QQ_STATUS, r'FY(dd)-QdMd') year,
        regexp_extract(YY_QQ_STATUS, r'FYdd-Q(d)Md') quartal,
        regexp_extract(YY_QQ_STATUS, r'FYdd-QdM(d)') month
    from `project.XXXXFinance.FINANCE_OPS_REPORT_V`
)
group by Program, ifnull(format('FY%s-Q%s', year, quartal), YY_QQ_STATUS)   

if applied to sample data in y our question - output is