Take the 2-minute tour ×

I have a table filled with tasks that users need to complete. Task B can't be started until Task A is completed, so my table looks something like this:

taskID   |   jobID   |   assignedTo   |   taskName   |   completedTime
1               1           Carl            blah 1          11235512
2               1           Mike            blah 2          13578423
3               1           Steve           blah 3          0
4               2           Alex            blah 4          12481235
5               2           Chris           blah 5          0
6               2           Steve           blah 6          0

Taking a look at Steve, I would want to select the job 1 - taskid 3 entry because every task (within the same job) before him has been completed and now it's his time to do his task for that job. However, I would NOT want to select the job 2 - taskid 6 for Steve because Chris hasn't completed job 2 - taskid 5.

Is there a way to do this in one query? Or would I have to get a list of all of Steve's tasks, then loop through each one and verify that it's the lowest taskID for each job that has a completedTime of 0?

share|improve this question
There is always a way to do it in a user defined function. But I assume you want a native query which selects all task for Steve grouped by jobId where the taskID is 1 higher than the highest taskID for this job with a completedTime other than zero. –  Alex Monthy Sep 6 '12 at 19:01
A subquery or a traditional JOIN would be the normal way of doing this. –  Burhan Khalid Sep 6 '12 at 19:01
add comment

2 Answers

nowtable.* -- or whatever fields you want
FROM yourtable AS nowtable
LEFT JOIN yourtable AS beforetable
  ON nowtable.jobID=beforetable.jobID 
  AND beforetable.taskID<nowtable.taskID
WHERE nowtable.assignedTo="Steve"
GROUP BY nowtable.taskID
HAVING IFNULL(COUNT(beforetable.taskID),0)=0
share|improve this answer
add comment

Are the tasks always added in order? Currently there's no way other than taskID to tell that taskIDs 1 and 2 should be completed before 3.

select top 1 *
from tblTasks 
where completedTime = 0 
 and jobID = (select top 1 jobid from tblTasks where assignedTo = @username and completedTime = 0 order by jobID)
 and assignedTo = @username
 order by taskID;
share|improve this answer
Yes, when a job is created all of the tasks are populated in to the database sequentially. –  Brds Sep 6 '12 at 19:18
Wouldn't this only give me 1 result (i.e. "top 1")... theoretically a user couldn't have unlimited number of tasks where they're next to act. –  Brds Sep 6 '12 at 19:22
add comment

Your Answer


