September 22, 2011 at 6:58 am
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
September 22, 2011 at 7:12 am
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 TotalCost
FROM myTable a
INNER JOIN (
SELECT productCode, MIN(cost) AS cost
FROM myTable
GROUP BY productCode
) b ON a.productCode = b.productCode
September 22, 2011 at 7:41 am
Thanks Cadaver,
I haven't tried this before and it is very, very useful.
All working happily now.
Regards, Greg
September 22, 2011 at 8:11 am
It's a form of correlated subquery
September 22, 2011 at 8:14 am
Me like...
September 22, 2011 at 12:52 pm
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
September 23, 2011 at 1:54 am
OUTER APPLY? I'll be looking that up.
September 23, 2011 at 2:24 am
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
)
September 23, 2011 at 4:10 am
Thanks to you both for adding in extra thought and comment.
Regards, Greg
September 23, 2011 at 6:23 am
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/61537Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy