Oracle SQL - only last reading before the 1st of next month

Question

Number field is the key but it can be modified several times a month (or not all all). Need to pull for last 6 complete month/year (Month_Year), only last reading before the 1st of next month.

question from:https://stackoverflow.com/questions/65909926/oracle-sql-only-last-reading-before-the-1st-of-next-month

Answer 1

So you want something like this?

select number, to_char(sysdate, 'Mon-YYYY') as month_year,
       max(reading) keep (dense_rank first order by modified_date_time desc) as reading
from t
group by number;

The most recent reading for each number along with the current date formatted as Mon-YYYY.