Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Last Up-Date 4

Status
Not open for further replies.
Feb 25, 2008
46
US
I have a query based on a table that has multiple entries for same PK differentiated by sequence number of the entries and date-times.

I am trying to pick the entries with the last (latest) date only, for the purpose of my query but it keeps picking all the entries.

I have tried Max, Last, DMax And Unique values on both the sequence number and date fields (one at a time and both at a time) but the problem persists.

Please help me with a condition I can enter in the criteria field of the query under date, which picks a single entry for each PK based by the latest date.

Thanks in advance.

-Mark.
 

Remou, the following is a sample of the data and the query.

Call_Id Seq_Nbr Date Type_Of_CallProduct
3567841 0 05/13/2009 11:44:58 PM Inquiry tires
3567842 0 05/14/2009 9:07:00 AM Inquiry rims
3567842 1 05/14/2009 3:40:00 AM repeat rims
3567842 2 05/15/2009 10:00:00 AM repeat rims
3567843 0 05/12/2009 12:45:00 PM Inquiry caps
3567844 0 05/16/2009 11:03:00 AM Inquiry accessories
3567844 1 05/17/2009 4:15:00 AM repeat accessories


SELECT call.Call_Id, Last(call.Seq_Nbr) AS LastOfSeq_Nbr, Max(call.Date) AS MaxOfDate, call.Type_Of_Call, call.Product
FROM call
GROUP BY call.Call_Id, call.Type_Of_Call, call.Product;


Thanks,
-Mark.
 
Select Call_id ,max(date)
from call
group by callid
 
sorry sb
Code:
Select Call_id ,max([date])
from call
group by call_id
 
I think I would go for:

Code:
SELECT call.Call_Id, call.Seq_Nbr, call.Date, call.Type_Of_Call, call.Product
FROM call
WHERE call.Date=(SELECT Max([Date]) 
                 FROM call c 
                 WHERE c.Call_ID=call.Call_ID)


 
SELECT A.Call_Id, A.Seq_Nbr, A.Date, A.Type_Of_Call, A.Product
FROM call AS A INNER JOIN (
SELECT Call_Id, Max([Date]) AS MaxOfDate FROM call GROUP BY Call_Id
) AS B ON A.Call_Id = B.Call_Id AND A.Date = B.MaxOfDate


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Sorry for responding so late.

thanks pwise, Remou and PHV all your queries worked.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top