Bigquery SQL MAX() text value

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

Goal:

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

SELECT  
    CASE WHEN UPPER(SCENARIO) LIKE '%ACTUALS%' THEN CONCAT(LEFT(FISCAL_YEAR,4) ,'-', QUARTER, '_ACTUALS')
        WHEN UPPER(SCENARIO) LIKE 'Q%M%' THEN CONCAT(LEFT(FISCAL_YEAR,4) ,'-', QUARTER, SUBSTR(SCENARIO,3,2))
        WHEN UPPER(SCENARIO) LIKE '%BUDGET%' THEN CONCAT(LEFT(FISCAL_YEAR,4) ,'-', QUARTER,  '_BUDGET')
        END AS SCENARIO,
CONCAT(LEFT(FISCAL_YEAR,4) ,'-', QUARTER) AS QUARTER,
PROGRAM_NAME,
FROM [XXXXFinance.FINANCE_OPS_REPORT_V] FIN  
WHERE PROGRAM_NAME = 'Kiev20'
    AND (XYZ_PER_NAME_QUARTER >= (SELECT XYZ_PER_NAME_QUARTER FROM [XXXXMaster_Data.CALENDAR] 
    WHERE CALENDAR_DATE_STR = CURRENT_DATE())
    OR XYZ_PER_NAME_QUARTER < (SELECT XYZ_PER_NAME_QUARTER FROM [XXXXMaster_Data.CALENDAR] 
    WHERE CALENDAR_DATE_STR = CURRENT_DATE()) AND (SCENARIO CONTAINS 'Actual' OR SCENARIO CONTAINS 'Budget')
    OR XYZ_PER_NAME_QUARTER < (SELECT XYZ_PER_NAME_QUARTER FROM [XXXXMaster_Data.CALENDAR] 
    WHERE CALENDAR_DATE_STR = CURRENT_DATE()) AND (SCENARIO CONTAINS 'Q%M%'))
group by 1,2,3
order by SCENARIO```

Answer

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