I have a database with a products table and another table product_details which has additional product details but this table also has historical entries for each product.
The key for both tables is the product_code.
For example:
Producttable hasproduct_codeABC123product_detailstable has 8 entries forproduct_codeABC123
I need to link both tables together so I can see the product details, but only want to see the most recent entry in the product_details table
so if I do this:
SELECT
prd.product_code,
prd.description,
dtls.price,
dtls.last_update
FROM
products prd
INNER JOIN product_details dtls ON prd.product_code = dtls.product_code
WHERE
prd.product_code = 'ABC123'
this will return many rows, I need 1 row… and I need it to work for all rows in the product table..
In code I can hit the product_details table with
SELECT top 1 *
FROM product_details
WHERE product_code = 'ABC123'
ORDER BY last_update DESC
which will give me the single line that I need but this will be super inefficient if I have to do this for every item in the product table every time i need to view the data.
is there any way in SQL to join product table to just the most recent product_details entry?
4
This approach sues a CTE to determine the latest product_details for each product_id – this approach isn’t the most terse or succint, but I feel is more maintainable, and results in as-good an execution-plan as you’ll get, provided you have an INDEX on product_details( product_code, last_update ).
Don’t forget to schema-qualify your table references (hence the dbo. bit) and parameterize your own queries.
If you use this often, you can convert the latest CTE to a VIEW WITH SCHEMABINDING for easier reuse.
DECLARE @productCode nvarchar(50) = N'ABC123';
WITH latest AS (
SELECT
pd.product_code,
MAX( pd.last_update ) AS last_update
FROM
dbo.product_details AS pd
GROUP BY
pd.product_code
)
SELECT
p.*,
pd.*
FROM
dbo.products AS p
INNER JOIN latest AS l ON l.product_code = p.product_code
INNER JOIN product_details AS pd ON
pd.product_code = l.product_code
AND
pd.last_update = l.last_update
WHERE
p.product_code = @productCode;