mysql - SQL query to obtain last proximate occurrence of a row (by timestamp) with a particular property -

this question regarding mysql table. suppose have change log table, reach row represents change of given type made on object user. e.g., table named changes:

 object_id   type    value   timestamp 1   update_name test1   1/1/15 1   update_site site1   1/1/15 1   add_budget  $10 1/1/15 1   add_expense $5  1/1/15 1   update_time 10  1/20/15 1   update_time 8   1/31/15 2   update_name site2   1/2/15 2   remove_budget   $2  2/1/15 2   update_name newsite2    2/15/15 1   update_time 12  2/7/15 1   update_name newtest1    2/7/15 1   add_expense $4  2/7/15 

suppose wanted find changes object_id 1 on 2/7/15:

select * changes object_id=1 , timestamp='2015-02-07';

i back:

 1   update_time 12  2/7/15 1   update_name newtest1    2/7/15 1   add_expense $4  2/7/15 

suppose wanted understand in single query matching results, last proximate change matching type same object_id. so, example, while seeking 3 rows in above query 2/7/2015, i'd following rows (in single query):

 1   update_time 8   1/31/15 1   update_name test1   1/1/15 1   add_expense $5  1/1/15 

why? because in original query, these same change types , want able compare them last changes of types. so, 1 update_time 8 1/31/15 last change of type update_time on object_id 1, 1 update_name test1 1/1/15 last change of type update_name on object_id 1, , 1 add_expense $5 1/1/15 last change of type add_expense on object_id 1.

so, in single query, i'd clean list shows before , after values this:

 1   add_expense $4  2/7/15 1   add_expense $5  1/1/15 1   update_time 12  2/7/15 1   update_time 8   1/31/15 1   update_name newtest1    2/7/15 1   update_name test1   1/1/15 

so, end is: gimme changes happened on object_id 1 on 2/7/2015 , show me last value each of changes. can see, example, when time updated on 2/7, updated 8 12; similarly, when name updated on 2/7, updated test1 newtest1.

considerations: - derive final list in single query...is possible? - reminder: inputs object_id , date. have no idea how many rows come , of type be. - fyi: there possibility there no previous change of type. i.e., first change of particular type not have prior change of type. fine, didn't know if matters solution. - object_id 2 immaterial in sample data. added demonstrate there various other object_id's in table.

update:

my original post not clear. have updated sample data , desired output better illustrate need.

ok. how can see them per column (on same line).

select l2.object_id, l2.type, l2.timestamp, l2.value, l3.timestamp, l3.value (select l1.object_id, l1.type, l1.value, l1.timestamp    log l1 l1.object_id=1 , l1.timestamp='2015-02-07') l2 left outer join log l3 on (l3.object_id = l2.object_id ,                             l3.type = l2.type , l3.timestamp=(select max(x.timestamp)                                                                 log x                                                                 x.object_id=l2.object_id ,                                                                      x.type=l2.type ,                                                                       x.timestamp<'2015-02-07')) 

and how see them want per line.

select l7.object_id, l7.type, l7.value, l7.timestamp  (select l2.object_id, l2.type, l2.timestamp timestamp1, l3.timestamp timestamp2 (select l1.object_id, l1.type, l1.timestamp    log l1 l1.object_id=1 , l1.timestamp='2015-02-07') l2 left outer join log l3 on (l3.object_id = l2.object_id ,                             l3.type = l2.type , l3.timestamp=(select max(x.timestamp)                                                                 log x                                                                 x.object_id=l2.object_id ,                                                                      x.type=l2.type ,                                                                       x.timestamp<'2015-02-07'))) f inner join log l7 on (l7.object_id=f.object_id , l7.type=f.type ,                      ((l7.timestamp=f.timestamp1) or (l7.timestamp=f.timestamp2)))  order l7.type, l7.timestamp