Value from Field [Price] for most recent dated record

[LittleSmudge]

tblPricing
PricingId - Prine Key
PropRef - Foreign Key
PropPrice - Currency
DatePublished - Date

For a given [PropRef] there can be none, one or many record in the table.
I need a query that will produce one record per [PropRef] with the value in [PropPrice] which is the most recent ( according to the
[DatePublished] ). Max[DatePublished]

Note: this is not necessarily the most recent one in the table ( Highest PricingId ) as record order in the table can't be guaranteed.

I know I ought to be able to sort this but after too many years away from writing SQL my brain has gone rusty in that department.

Help someone please.

G LS

 

[Andrzejek]

Just a 'shot in the dark', not tested:

Select PropRef, PropPrice, DatePublished
From tblPricing, [blue](Select PropRef, MAX(DatePublished) As MaxDate
From tblPricing Group By PropRef) As X[/blue]
Where PropRef = [blue]X. PropRef[/blue]
And DatePublished = [blue]X.MaxDate[/blue]

If you tell me it works, I am going to Vegas


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

If Andy's doesn't work (which I expect it does) you can also try something like:

SELECT *
FROM tblPricing
WHERE PricingID =  
(SELECT TOP 1 PricingID
 FROM tblPricing P
 WHERE P.PropRef = tblPricing.PropRef
 ORDER BY P.DatePublished DESC, PricingID)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[LittleSmudge]

Andy & Duane - my grateful thanks to you both.
Both approaches work but with only 48 records so far I'm unable to assess any difference is speed.

And, as expected - my first reaction was - "Oh yes, nest two queries"
You've stirred long dormant memories. Thanks once again.

G LS