Post reply

Joining, but need to find the row with the min values in it

  • September 22, 2011 at 6:58 am

    #248573

    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

    #1385127

    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

    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/

  • September 22, 2011 at 7:41 am

    #1385150

    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

    #1385181

    It's a form of correlated subquery

  • September 22, 2011 at 8:14 am

    #1385187

    Me like...

  • September 22, 2011 at 12:52 pm

    #1385389

    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

    #1385559

    OUTER APPLY? I'll be looking that up.

  • September 23, 2011 at 2:24 am

    #1385569

    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

    #1385595

    Thanks to you both for adding in extra thought and comment.

    Regards, Greg

  • September 23, 2011 at 6:23 am

    #1385654

    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 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

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