470,631 Members | 1,704 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,631 developers. It's quick & easy.

show only last item per ID

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!

Nov 13 '05 #1
5 1302
On 20 Oct 2005 06:34:43 -0700, 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!


I only know how to do it if the table has a single field that can be used as a
key - e.g. an Autonuber field.

If we have this table structure...

tblTaskCase
TaskCaseID
Task
CaseID
Time

...then a query to do the job might be...

SELECT TC.*
FROM tblTaskCase TC
WHERE TC.TaskCaseID =
( SELECT TOP 1
TaskCaseID
FROM tblTaskCase TC2
WHERE TC2.Task = TC.Task AND
TC2.CaseID = TC.CaseID
ORDER BY TC2.Time Desc,
TC2.CaseID
)
Nov 13 '05 #2
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).
Nov 13 '05 #3
Thanks to both of you, i have something that will works!

Nov 13 '05 #4

ps*****@gmail.com wrote:
Thanks to both of you, i have something that will works!

That doesn't require 2 queries......assuming I understood what you were
wanting, I recreated your scenario and got the same results you were
expecting with this:

SELECT Max(x.Task) AS Task, x.CaseID, Max(x.Time) AS [Latest Time]
FROM TaskTable AS x
GROUP BY x.CaseID;
James

Nov 13 '05 #5
James wrote:
ps*****@gmail.com wrote:
Thanks to both of you, i have something that will works!


That doesn't require 2 queries......assuming I understood what you were
wanting, I recreated your scenario and got the same results you were
expecting with this:

SELECT Max(x.Task) AS Task, x.CaseID, Max(x.Time) AS [Latest Time]
FROM TaskTable AS x
GROUP BY x.CaseID;
James


Ahh, but your query won't work as expected if, say, Task A in a given
case was the last one completed...

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Christof Nordiek | last post: by
2 posts views Thread by dauwe.peter | last post: by
23 posts views Thread by Florian Lindner | last post: by
5 posts views Thread by dudeja.rajat | last post: by
reply views Thread by Fredrik Lundh | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.