Last Up-Date 4

[markedback]

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]

Please post a little sample data and one of the SQL statements that you tried.

http://lessthandot.com

 

[markedback]

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.

 

[PWise]

Select Call_id ,max(date)
from call
group by callid

 

[PWise]

sorry sb

Select Call_id ,max([date])
from call
group by call_id

 

[Remou]

I think I would go for:

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)

http://lessthandot.com

 

[PHV]

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

 

[markedback]

Sorry for responding so late.

thanks pwise, Remou and PHV all your queries worked.