How to get the latest record on big query based on field date

Question

This is my query

SELECT producto, ROUND(precio_medio,2) as precio, fecha, mpio, cod_dpto, tipo, presentacion FROM DATABASE where categoria="Arrendamiento de tierras" and tipo="Arrendamiento de tierras" and producto="Cabeza de ganado mensual para pastoreo, terreno inclinado/ondulado con agua" and presentacion ="Arriendo"

it return [id, producto, precio, fecha,mpio,cod_dpto, presentacion]

So I want to get only the lates record of each one Popayan and Piendamo based in the field fecha

Answer 1

In Big Query, you can use arrays for this. Assuming that you want the row with the most recent fecha per mpio:

select * except(ar)
from (  
    select 
        mpio,
        array_agg(
            struct(id, producto, precio, mpio,cod_dpto, presentacion)
            order by fecha desc limit 1
        ) ar
    from database
    where 
        categoria="Arrendamiento de tierras" 
        and tipo="Arrendamiento de tierras" 
        and producto="Cabeza de ganado mensual para pastoreo, terreno inclinado/ondulado con agua" 
        and presentacion ="Arriendo"
    group by mpio
) x, unnest(x.ar)

A more standard approach is to use window functions:

select * except(rn)
from (
    select 
        fecha, id, producto, precio, mpio,cod_dpto, presentacion,
        row_number() over(partition by mpio order by fecha desc) rn
    from database
    where 
        categoria="Arrendamiento de tierras" 
        and tipo="Arrendamiento de tierras" 
        and producto="Cabeza de ganado mensual para pastoreo, terreno inclinado/ondulado con agua" 
        and presentacion ="Arriendo"
) t
where rn = 1