I have a table with Forecast, Budget, & Actuals. I need to grab the latest data based on the month which is prefixed in the d_period field (Ex: Jan, Feb, Mar, Apr, etc.). The query should always default to the latest month (no relation to current month - the upload varies so it could be 2 months ago, 4 months ago, or 1 month in the future). I just always need to grab data for the highest month of the year

From the below table I would just need to extract the data associated with Aug since this is the highest month. The table will only have current years data so don't need to worry about duplicated months spanning various years.

I'm not sure what the right approach is achieve this. I assume this needs to be SPLIT but not sure how to then grab the max month. Do I need to create a staging table with the month mappings and then use a MAXBY() possibly? i.e. {(Jan, 1), (Feb, 2).... etc.}

CodePudding user response：

Here's the solution. I did it in MySql using str_to_date because it has the same date functions as Presto.

p.s
Presto was rebranded as Trino in December of 2020.

select  d_period
       ,spend
from 
       (
        select t.*
              ,dense_rank() over (order by month(str_to_date(substring(d_period,1,3), '%b')) desc) as rnk
        from   t
        ) t
where   rnk = 1

Fiddle