SQL for last changed value before given index

Question

My table tracks changes to a value that is otherwise constant.

For example this is what it might look like if resources 1 and 2 start with values of 100 and 50 respectively - then resource 1 increases to 110 on day 5 (but resource 2 is unchanged), then resource 2 changes to 60 on day 7.

 resource_id | date_id | value
 ------------+---------+------
 1           | 1       | 100
 2           | 1       |  50
 1           | 5       | 110
 2           | 7       |  60 

Is there a simple query to get the value of the resources on a specific day? Something like:

SELECT resource_id, date_id, value FROM Resources WHERE ??? -- day = 6

---

resource_id | date_id | value
------------+---------+------
1           | 5       | 110
2           | 1       |  50

Note: I don't want interpolation of the values - just the last set value.

Answer 1

I think this should work:

SELECT resource_id, 
       date_id, 
       value 
FROM   resources, 
       (SELECT resource_id, 
               Max(date_id) AS date_id 
        FROM   resources 
        WHERE  date_id <= 6 
        GROUP  BY resource_id ) temp 
WHERE  resources.resource_id = temp.resource_id 
       AND temp.date_id = resources.date_id 

SQLFiddle Demo