Get last record by month/year and id-CodePudding

I need to get the last record of each month/year for each id.

My table captures daily, for each id, an order value which is cumulative. So, I need that at the end I only have the last record of the month for each id.

I believe without something simple, but with the examples found I could not replicate for my case.

Here is an example of my input data and the expected result: db_fiddle.

My attempt doesn't include grouping by month and year:

  select ar.id, ar.value, ar.aquisition_date
    from table_views ar
    inner join (
        select id, max(aquisition_date) as last_aquisition_date_month
        from table_views 
        group by id
    )ld
        on  ar.id = ld.id and ar.aquisition_date = ld.last_aquisition_date_month

CodePudding user response：

You could do this:

with tn as (
  select
    *,
    row_number() over (partition by id, date_trunc('month', aquisition_date) order by aquisition_date desc) as rn
  from table_views
)
select * from tn where rn = 1

The tn cte adds a row number that counts incrementally in descending order of date, for each month/id.. Then you take only those with rn=1, which is the last aquisition_date of any given month, for each id