Retrieiving records by Date

[wdverner]

Hi All,
I have a table that contains info about products:

Product: Product 1
Price: 6.99
Effective From: 01/01/2002

Product: Product 1
Price: 9.99
Effective From: 01/01/2006

As you can see I record any changes in price so that we have an historical record of price increases.

What i want to do is generate a price List of all my products from this table.

However I only want a unique list with the most up to date Price. i dont want repetition of products...so for the data above my query would return:

Product 1
9.99
01/01/2006

(It will NOT SHOW Product 1 at 6.99 as this is an old price replaced by the 9.99 price on 01/01/2006).

How can I create such a query?

Many thanks for your help and ideas!

 

[SkipVought]

Hi,

How about

Select Product, Price, Max(Effective From])...

?

Dont forget Group By Product, Price

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[PHV]

SELECT A.*
FROM yourTable AS A INNER JOIN (
SELECT Product, Max([Effective From]) AS LastDate FROM yourTable GROUP BY Product
) AS L ON A.Product = L.Product AND A.[Effective From] = L.LastDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886