qury to select most recent w/o duplicates
-
Here is the situation:
I have a table where the data is like:
tblDocument
PK DocID Name path version
1 1000 new.pdf c:\emp 1.0
2 1000 new.xls c:\dir 1.0
3 1000 new.xls c:\dir 1.1
4 1001 xyz.pdf c:\tmp 1.0
and a table that holds date information about this record:\
tblStatus
PK ObjectID UserID DateType date
99 1 999 CREATE 1/1/05
100 2 999 MODIFY 1/2/05
101 3 999 MODIFY 1/3/05
104 4 999 CREATE 1/4/05
where objectID is the PK of tblDocument
I want to do a query that will show
DocID Name date
1000 new.xls 1/3/05
1001 xyz.pdf 1/4/05
the most recent activity on a docID. any ideas? I tried playing with groups but to no success.
thanx in advance.
-
Try this one:
select distinct d.docid, d.name, s.date
from tblDocument d inner join tblstatus s on d.pk = s.objectid
where not exists
(select * from tblDocument d2 inner join tblstatus s2 on d2.pk = s2.objectid
where d.docid = d2.docid and s.date < s2.date)
-
What are the fields that are common to the two tables?
Are you linking tblStatus.ObjectID to tblDocument.PK?
if so,
select DocID, Name , date
from tblDocument d
join tblStatus s on s.ObjectID to d.PK
join ( select max (PK) as maxPK, ObjectID
from tblStatus
group by ObjectID) dt on s.PK = dt.maxPK
Logic: You need to link the two tables, and then link to a derived table that has only the most recent entry, using the highest (max) PK.
Good Luck,
Sara
-
thank you sara, it worked like a charm with a little modification of the fields that I wanted. Thanx again
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply