Confusing SQL Query, Group By? Having?

Question

I have a relational database in SQL Server which I use to store Products, Competitor Companies and Competitor Prices. I regularly add new records to the Competitor Prices table rather than updating existing records so I can track prices changes over time.

I want to build a query which given a particular product, find the most recent price from each of the competitors. It is possible that each competitor doesn't have a price recorded.

Data Example

tblCompetitorPrices

+-----+----------+-------------+-----+----------+
|cp_id|product_id|competitor_id|price|date_added|
+-----+----------+-------------+-----+----------+
|1    |1         |3            |70.00|15-01-2014|
+-----+----------+-------------+-----+----------+
|2    |1         |4            |65.10|15-01-2014|
+-----+----------+-------------+-----+----------+
|3    |2         |3            |15.20|15-01-2014|
+-----+----------+-------------+-----+----------+
|4    |1         |3            |62.30|19-01-2014|
+-----+----------+-------------+-----+----------+

And I want the query to return...

+-----+----------+-------------+-----+----------+
|cp_id|product_id|competitor_id|price|date_added|
+-----+----------+-------------+-----+----------+
|4    |1         |3            |62.30|19-01-2014|
+-----+----------+-------------+-----+----------+
|2    |1         |4            |65.10|15-01-2014|
+-----+----------+-------------+-----+----------+

I can currently access all the prices for the product, but I'm not able to filter the results so only the most recent price for each competitor is shown - I'm really unsure...here is what I have so far....

SELECT cp_id, product_id, competitor_id, price, date_added 
FROM tblCompetitorPrices
WHERE product_id = '1' 
ORDER BY date_added DESC

Thanks for any help!

Answer 1

As an alternative, you can also use ROW_NUMBER() which is a Window function that generates sequential number.

SELECT  cp_id,
        product_id,
        competitor_id,
        price,
        date_added
FROM    (
            SELECT  cp_id,
                    product_id,
                    competitor_id,
                    price,
                    date_added,
                    ROW_NUMBER() OVER (PARTITION BY competitor_id
                                        ORDER BY date_added DESC) rn
            FROM    tblCompetitorPrices
            WHERE   product_ID = 1
        ) a 
WHERE   a.rn = 1

This query can easily be modified to return latest record for each competitor in every product.