mysql - Latest Variable out of versioned pool -

for project, need keep track of huge amount of variables. needs logged, user changes variable etc. table looks (simplified 1 variable-type) (id surogate key tell versioned entries apart):

id | variable_id | change_time | change_user | change_task | revision | value 1    1             date     1             123           1          hello world 2    2             date     1             123           1          22.5 3    1             date     2             456           2          foo bar 

to retrieve latest set of revisions (here entries 2 , 3) use solution described on here: retrieving last record in each group

the resulting query looks this:

select     v1.id,    v1.value     variable_history v1 left join    variable_hisotry v2  on    (     v1.variable_id = v2.variable_id ,      v1.revision < v2.revision   )     v2.id null 

now need modify query, following rules apply:

• querying latest variables should return row 2 , 3
• querying latest variables for task 456 should return row 2 , 3
• querying latest variables for task 123 should return row 1 , 2 (for historic purpose)

the tricky thing is, not each task changing (updating) variables time, can't simple query variables of task x. instead have take variables task x, if there any, other variables ones task < x highest revision. (and exclude revisions of later task)

for case, result shoud contain variables. in small example, should have 2 rows.

edit:

---

the query

 select         v1.id,        v1.value             (select * variable_history change_task <= 123) v1     left join        (select * variable_history change_task <= 123) v2      on        (         v1.variable_id = v2.variable_id ,          v1.revision < v2.revision       )             v2.id null 

works expected. having 10.000 entrys daily i'm not happy double sub-select followed join...

http://sqlfiddle.com/#!2/cfda03/2

edit: hmm, on other side: each row of variable_history bound entity_id, , ofc. limiting 2 sub selects 1 entity in question reduce table size sth. 150 different variables... shouldn't performance problem.

select v.*   (          select   variable_id, max(revision) revision              variable_history          group variable_id        ) very_latest left join (          select   variable_id, max(revision) revision              variable_history             change_task = ?          group variable_id        ) task_latest using (variable_id)   join variable_history v     on v.variable_id = very_latest.variable_id    , v.revision = coalesce(task_latest.revision, very_latest.revision)