Rodrigo Formighieri Rodrigo Formighieri - 1 month ago 10
SQL Question

query to get quantity from last recorded period in month postgresql

I've been trying to make this query for so long, that I just gave up and came here to ask you some help. Really guys, many mornings, afternoons spent only trying to formulate this single single single query, almost loosing my mind. Anyway, I will be direct with you, here we go:

I have a table:

date | quantity | id
------------+----------+----
2016-06-10 | 438 | 27
2016-06-17 | 449 | 28
2016-06-24 | 458 | 29
2016-07-01 | 466 | 30
2016-07-08 | 468 | 31
2016-07-15 | 468 | 32
2016-07-22 | 473 | 33
2016-07-29 | 473 | 34
2016-08-05 | 475 | 35
2016-08-12 | 479 | 36
2016-08-19 | 488 | 37
2016-08-26 | 498 | 38
2016-09-02 | 519 | 39


I need to get the quantity from the last recorded day inside each month. I mean, from the table above, I need the following rolls:

date | quantity | id
------------+----------+----
2016-06-24 | 458 | 29
2016-07-29 | 473 | 34
2016-08-26 | 498 | 38


and the final table that I really need is this:

month | quantity
------------+----------
06 | 458
07 | 473
06 | 498


I trying GROUP, HAVING, MAX, JOINS, UNIONS anything you can imagine, buy just can't get through it. Any ideas to make this happen?

Thanks

Answer

Postgres has a great feature called distinct on:

select distinct on (date_trunc('month', date)) t.*
from t
order by date_trunc('month', date), date desc;

distinct on returns exactly one row for each value of the keys in parentheses -- in this case, one per month. Which row? The first row encountered in the data. So, this returns the latest date each month.