Joining, but need to find the row with the min values in it
-
Hi,
I am LEFT joining to a lookup table by product code to find a product cost. The problem is that in the lookup table there are multiple rows for this product code, and I want to use the min(cost).
In my SQL, I want to multiply a main table value (product sales) by the lookup tables cost value for that product.
I feel I'd like to write...
SELECT a.sales * b.cost as TotalCost ... WHERE b.cost = min(b.cost).
Obviously this is "fantasy SQL", but maybe you can see what I'd like to achieve !
I hope this is clear, if not let me know and I'll add in some test data.
Regards, Greg
-
greg.bull (9/22/2011)
Hi,I am LEFT joining to a lookup table by product code to find a product cost. The problem is that in the lookup table there are multiple rows for this product code, and I want to use the min(cost).
In my SQL, I want to multiply a main table value (product sales) by the lookup tables cost value for that product.
I feel I'd like to write...
SELECT a.sales * b.cost as TotalCost ... WHERE b.cost = min(b.cost).
Obviously this is "fantasy SQL", but maybe you can see what I'd like to achieve !
I hope this is clear, if not let me know and I'll add in some test data.
Regards, Greg
Subquery. e.g.
SELECT a.productCode, a.sales * b.cost AS TotalCostFROM myTable a
INNER JOIN (
SELECT productCode, MIN(cost) AS cost
FROM myTable
GROUP BY productCode
) b ON a.productCode = b.productCode
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Thanks Cadaver,
I haven't tried this before and it is very, very useful.
All working happily now.
Regards, Greg
-
It's a form of correlated subquery
-
Me like...
-
With appropriate indices, a CROSS/OUTER APPLY should perform better, but you should test it with your actual data. An OUTER APPLY corresponds to the LEFT JOIN that you are currently using.
SELECT a.productCode, a.sales * b.cost AS TotalCost
FROM myTable AS a
OUTER APPLY (
SELECT TOP (1) productCode, cost
FROM myTable
ORDER BY cost
WHERE a.productCode = b.productCode
) AS b
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
OUTER APPLY? I'll be looking that up.
-
I tend to get better results with an old-school subquery.
Here's a real-world example where we have an invoice sales table and product costs table with effective-from dates.
We need the most recent cost for a given product that applies to the invoice.
SELECT a.InvoiceQty * b.cost as TotalCost
FROM Sales a, ProductCosts b
WHERE a.ProductCode = b.ProductCode -- join to get the cost we want
AND a.InvoiceDate >= b.CostingDate -- where the cost wasn't applied after the invoice
AND b.CostingDate = (
SELECT max(c.CostingDate) -- the most recent bit
FROM ProductCosts c
WHERE c.ProductCode = b.ProductCode -- for the given product, this could join to a
)
-
Thanks to you both for adding in extra thought and comment.
Regards, Greg
-
Another way using ROW_NUMBER
WITH OrderedCosts AS (
SELECT ProductCode,Cost,
ROW_NUMBER() OVER(PARTITION BY ProductCode ORDER BY Cost) As rn
FROM ProductCosts)
SELECT a.ProductCode, a.InvoiceQty * b.Cost as TotalCost
FROM Sales a
INNER JOIN OrderedCosts b ON a.ProductCode = b.ProductCode
AND b.rn=1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply