ps*****@gmail.com wrote:
Hi
I have 3 colums: 'Task' , 'Case ID' and ' Time'
And i would like to have an overwiew of the last task (the taks with de
latest time) of every Case ID.
Example
Task Case ID Time
A 1 12:00
B 1 12:15
C 1 12:20
D 1 12:30
A 2 12:10
B 2 12:15
D 2 12:20
E 2 12:25
In this example, i would like to show only 'D,1, 12:30' and 'E,2,12:25'
How should I do this?
Thanks!
It basically requires two queries. If I have to reuse the first query,
I'll save it as a separate query, instead of just embedding the SQL into
another query as a subquery, like I did for the second query.
We'll start with this:
-----------------------------------------------
select [case id] as case, max(time) as time
from tasktable
group by [case id]
-----------------------------------------------
case time
1 12:30
2 12:25
Now, use the a query as a subquery.
paste this into a SQL query window. When you save it and reopen it, the
coding around the subquery will be changed, but it'll still work. It'll
be a PITA to edit correctly after this, though...):
-----------------------------------------------
select t.task, t.[case id], t.time
from tasktable as t
left join (
select [case id] as case, max(time) as time
from tasktable
group by [case id]) as tm
on
t.caseid = tm.case
and t.time >= tm.time
-----------------------------------------------
task case id time
D 1 12:30
E 2 12:25
In SQL books, it's commonly called a "theta join", if they mention it at
all. Needless to say, most query builder tools can't display them
graphically, so you get to edit them only in SQL View... Theta joins are
incredibly powerful, and incredibly underutilized, for things like the
above query (i.e., ranges, dealing with floating point roundoffs, etc).
