How to retrieve all columns in a table under a max date condition on BigQuery?-CodePudding

I have a table that contains the following features:

Month (INT64)
COP (FLOAT64)
USD (FLOAT64)
EUR (FLOAT64)
ARS (FLOAT64)
CLP (FLOAT64)
PEN (FLOAT64)
MXN (FLOAT64)

This table contains the exchange rates from several currency to a USD.

The query that generates this table is the following:

SELECT 
    CAST(MonthNum as INT64) as Month,
    CAST(COP as FLOAT64) as COP,
    CAST(USD as FLOAT64) as USD,
    CAST(EUR as FLOAT64) as EUR,
    CAST(ARS as FLOAT64) as ARS,
    CAST(CLP as FLOAT64) as CLP,
    CAST(PEN as FLOAT64) as PEN, 
    CAST(MXN as FLOAT64) as MXN,
FROM 
    `xxx.regional.xr`
WHERE 
    Type = 'INTRA'
ORDER BY 
    Month DESC

Here an example:

I'd like to retrieve the same columns but under the condition MAX(Month), this is, all columns, but just for the maximum month the greater MonthNum. I'm new in SQL, and I found out that MAX() function can not be used in the WHERE condition. How can I do that?

CodePudding user response：

How about simple LIMIT 1

SELECT 
  CAST(MonthNum as INT64) as Month,
  CAST(COP as FLOAT64) as COP,
  CAST(USD as FLOAT64) as USD,
  CAST(EUR as FLOAT64) as EUR,
  CAST(ARS as FLOAT64) as ARS,
  CAST(CLP as FLOAT64) as CLP,
  CAST(PEN as FLOAT64) as PEN, 
  CAST(MXN as FLOAT64) as MXN,
FROM `xxx.regional.xr`
WHERE Type='INTRA'
ORDER BY Month DESC
LIMIT 1

CodePudding user response：

Use a subquery:

SELECT 
CAST(MonthNum as INT64) as Month,
CAST(COP as FLOAT64) as COP,
CAST(USD as FLOAT64) as USD,
CAST(EUR as FLOAT64) as EUR,
CAST(ARS as FLOAT64) as ARS,
CAST(CLP as FLOAT64) as CLP,
CAST(PEN as FLOAT64) as PEN, 
CAST(MXN as FLOAT64) as MXN,
FROM `xxx.regional.xr`
WHERE Type='INTRA'
AND MonthNum IN (SELECT MAX(MonthNum) FROM `xxx.regional.xr`)
ORDER BY Month DESC