How can I get latest record?

[Alaçati Turizm Yatirim ve Isletme A.S.]

I have created a simple table where I put my competitors' prices with
date information. I want to create a query to get their prices for a certain
product. When I do this, I end up with prices that were valid 2 years ago as
well as today's prices for the same company. How can I make a query so that
I only a firm's latest prices for a product?

Thanks for your time,

 

[Sandra Daigle]

You can do this with a subquery. Basically the results of the subquery
should be 1 column and 1 row and this result is used as the criteria for the
main select query. Here is an example: This is from my application and it
returns the most recent price for a part.

SELECT
tblPricesHist.Partid,
tblPricesHist.LastUpdated,
tblPricesHist.Price
FROM
tblPricesHist
WHERE
tblPricesHist.LastUpdated=
(select
max(lastupdated)
from
tblpriceshist as ph
where
ph.partid=tblpriceshist.partid
group by
ph.partid);

You do the same with an Inner Join on a nested query:

SELECT
tblPricesHist.Partid,
tblPricesHist.LastUpdated,
tblPricesHist.AmtPerUOM
FROM
tblPricesHist
Inner Join (
select
partid,
max(lastupdated) as maxdate
from
tblpriceshist
group by
partid) as ph
on ph.partid=tblpriceshist.partid and
ph.maxdate=tblpriceshist.lastupdated