Skip to content
Advertisement

Oracle SQL closest date to given date

i have a table with a column for categories, date and price. Like this:

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:

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:

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement