Unanswered: Finding the lowest value where two tables are joined
I have a table called 'Tasks' and another called 'Queues'. Each task is added to the Task table and has a reference to a row in Queues. There is also a field called TimeModified for each task, which represents the time a task is created or modified. What I want is some way of selecting the queue with the earliest TimeModified value. But the Queues table will not have this field, but it should somehow find it from the Tasks within the queue.
QueueId | Active
1 | Yes
2 | Yes
Task Id | TaskModified | QueueId | TaskComplete
100 | 10/06/2012 10:00 | 1 | No
101 | 10/06/2012 10:01 | 1 | No
102 | 10/06/2012 10:02 | 2 | No
Imagine the tasks above are submitted in sequence. At 10:03 processing is completed on the earliest task which is 100. The table then looks like this:
So then processing begins on the next task. But what is the next task? It is not 101 even though it has the earliest timestamp. It is 102 because it is Queue 2 that has the earliest timestamp of 10:01 as Queue 1 has the timestamp of 10:03.
To give you an idea of what could be done, when the above state is reached a query could look like this:
Here you see that it is Queue 2 that has the earliest modified timestamp. From this query another query could be done to find the task within queue 2 that has the earliest modified timestamp (which is 102) and work on that. Ideally my queue should show the TaskId as well but it's not a must.