finding the row with the latest date

[Honestmath]

hey all,

i have table with 3 fields:

ID EntryDate Price

ID's are repeated many times for different dates and the same dates appear for many ID's. ID and EntryDate together are the primary key.

i want to find the latest price for each ID. something like:

select * from tblPrices
where EntryDate=Max(EntryDate for ID)

anyone know the most effecient way to do this? can it be done in 1 query?

thnx for the help -- math

 

[illuzioner]

Hi Math,

try:

SELECT DISTINCTROW tblPrices.ID, tblPrices.EntryDate, tblPrices.Price
FROM tblPrices
WHERE (((tblPrices.EntryDate)=DMax("[EntryDate] ","tblPrices","[ID]=" & [tblPrices]![ID])));

Regards,

Lou

 

[cmmrfrds]

This should be close.

Select * from tblPrices A
Inner join
(Select Max(EntryDate) as maxD from tblPrices
group by ID) B
ON A.ID = B.ID and A.EntryDate = B.maxD

 

[Honestmath]

thnx to both, they both work.
i have some problems with cmmrfrds' solution in that access doesn't like it in other queries and keeps changing the '(' to '[' and complains it can't find the table.

first one work, so its cool, though.

thnx again -- math

 

[cmmrfrds]

The solution I showed was using a derived query which will work in most sql engines, but Access likes to do it by building up from 2 queries. Make query1 and then include in query2 for the same effect.

query1
Select ID, Max(EntryDate) as maxD from tblPrices
group by ID)

query2
Select * from tblPrices
inner join query1 ON
(tblPrices.EntryDate = query1.maxd) and
(tblPrices.ID = query1.ID)