sql - Group records by certain fields but only if they're "next" to each other -

i need group records in table based on odd requirements. i've gotten close need, can't last bit.

id  task    time 1   task1   6:00 1   task1   6:05 1   task1   6:10 1   task2   6:15 1   task2   6:20 1   task1   6:30 2   task1   9:00 2   task1   9:05 2   task2   9:10 

i need tasks grouped not if they're different id, , not if aren't "next" each other, , want return latest time group of tasks.

so, results of above should this:

id  task    time 1   task1   6:10 1   task2   6:20 1   task1   6:30 2   task1   9:05 2   task2   9:10 

i've figured out grouping task/id , getting latest time parts, not part grouping them if they're "next" each other (i.e. not separated different task).

here's have now:

select id, task, max(time) latest (select id, task, time tasktable) group id, task 

this produces following:

id  task    time 1   task2   6:20 1   task1   6:30 2   task1   9:05 2   task2   9:10 

as can see, of task1's id 1 grouped under latest time, instead of showing me 2 separate times ran (before , after task2).

oh yeah, , sql has work in access.... there's that.

this challenging in database, , more in ms access, limited implementation of modern sql functions.

but, there way. use correlated subquery count number of dissimilar tasks before given task. while tasks same, constant, can used aggregation.

select task, mint(time), max(time) (select t.*,              (select count(*)               tasks t2               t2.task <> t.task ,                     t2.id = t.id ,                     t2.time < t.time              ) grp       tasks t      ) t group grp, task