Sql Return A Value At A Specific Date In Time

I'm trying the find a value at a certain date. My data looks like Date Value 2013-11-02 5 2013-10-10 8 2013-09-14 6 2013-08-15 4 How can I determine what the value

Solution 1:

You can do it with order by and limiting the number of rows. In SQL Server syntax (and Sybase and Access):

select top 1 t.*fromtable t
wheredate<='2013-09-30'orderbydatedesc;

In MySQL (and Postgres):

select t.*fromtable t
wheredate<='2013-09-30'orderbydatedesc
limit 1;

In Oracle:

select t.*from (select t.*fromtable t
      wheredate<='2013-09-30'orderbydatedesc
    ) t
where rownum =1

EDIT:

And, a SQL standard way to it (should work in any database):

select t.*fromtable t
wheredate= (selectmax(date)
              fromtable t2
              wheredate<='2013-09-30'
             );