i have a table with a column for categories, date and price. Like this:
x
group 1 - 03.03.2019 - 5.00
group 1 - 03.02.2018 - 4.00
group 2 - 05.05.2019 - 2.25
group 2 - 05.05.2018 - 1.00
So there are (almost) always two dates per group with two different prices. Now i need to write an SQL Statement to get the closest date per group to a given date(f.e. 05.05.2019). Group 1 has two dates an the SQL statement needs to Select one of them which is the closest to the given date. This need to happen for all of the groups.
I tried it for a couple of hours but i am stuck. Thanks for ur help
Advertisement
Answer
Here is one option using not exists
:
select t.*
from mytable t
where not exists (
select 1
from mytable t1
where
t1.category = t.category
and greatest(t1.date, date '2019-05-05') - least(t1.date, date '2019-05-05')
< greatest(t.date, date '2019-05-05') - least(t.date, date '2019-05-05')
)
This gives you the “closest” record to 2019-05-05 for each group (whether before or after).
If, for example, you want the closest record before 2019-05-05, that’s a bit simpler:
select t.*
from mytable t
where
t.date <= date '2019-05-05'
and not exists (
select 1
from mytable t1
where t1.category = t.category and t1.date <= date '2019-05-05' and t1.date > t.date
)