Take data within a month, if not, then in the previous one-CodePudding

In my application, I use this select to get data in a month:

select e.id, e.date, e.local_currency_id, e.rate, e.downloaddate
from exchange_rates e
join (select local_currency_id, max(date) as max_date
from exchange_rates group by local_currency_id, date_trunc('month', date)) as m_date on e.date = m_date.max_date
where e.date >=:currentDate and e.local_currency_id = :localCurrencyId
order by date_trunc('month', e.downloaddate) desc limit 1

The question is that I want to get the data in the month (local_currency), sorting from the end of the month, in reverse order, and if there is no data in this month, then I need to take the data in the previous one.

Now, according to my select, I get data that is greater than my date, but I need to get the data as I described above

CodePudding user response：

not totally following your logic by how about use row_number() to do a ranking. partition by the currency id and order by the date descending. then just pick the ranking of 1. (the most current)

with t as (select e.*,
row_number() over (partition by e.local_currency_id order by date_trunc('month', e.downloaddate) desc) as rn
from exchange_rates e
where e.local_currency_id = :localCurrencyId)
select id, date, local_currency_id, rate, downloaddate
from t
where rn = 1