0

I've got the following table structure.

Table 1

id  Value   start_date  
1   1000    09-05-2010  
2   2000    10-05-2011  
3   3000    11-05-2012  
4   4000    17-05-2013
5   5000    18-05-2014

Looking at this table, I would like to know which value (which will be 3000) will be the correct one for a record in another table with date 01-01-2013.

My challenge is that I'm not able to define which is the last date before the given one (01-01-2013 in my example).

Thanks!

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Javi Gonzalez
  • 3
  • 2

2 Answers2

0

You can SELECT the dates inferior to the one you choose (eg 01-01-2013) and use LIMIT to get the nearest.

SELECT value from my_table WHERE start_date < mydate LIMIT 1
neer
  • 4,031
  • 6
  • 20
  • 34
user5329034
  • 110
  • 1
  • 1
  • 8
  • I've got trouble with the given date 'mydate' as it's a date stored in a different table and as it's a relational database I need to add other several tables where the links are established between these two tables. – Javi Gonzalez Jul 13 '16 at 10:55
  • You can select your data from multiple tables, or use a JOIN procedure. See http://stackoverflow.com/questions/12890071/select-from-multiple-tables-mysql – user5329034 Jul 13 '16 at 11:00
0

In Oracle 12c+, you can do this as:

select t2.*,
       (select t1.value
        from table1 t1
        where t1.date <= t2.date
        order by t1.date
        fetch first 1 row only
       ) as value
from table2 t2;

In earlier versions, you can use keep:

select t2.*,
       (select max(t1.value) keep (dense_rank first order by date desc)
        from table1 t1
        where t1.date <= t2.date
       ) as value
from table2 t2;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786