Search code examples
sqlmaxmin

How to return MAX and MIN of a value from a table?

I want to get the last cost with latest costing date and minimum cost for products.

When I use the query below, it is giving me the Max Date and Min Cost for each column. Please see the screenshots below.

SELECT MAX(CostingDate) AS LatestDate,
MIN(Cost) AS MinPrice,
OutletCode,
ProductID  
FROM AccountsCosting
WHERE OutletCode = 'C&T01'
GROUP BY OutletCode, ProductID

Result:

enter image description here

E.g - for productID: 200006

SELECT * FROM AccountsCosting
WHERE ProductID = 200006 AND OutletCode = 'C&T01'
ORDER BY CostingDate DESC

enter image description here

What I want is the last costing date with the minimum cost (the one that I highlighted with red color). Even if the purchase date is the same 2013-03-20, it should return the minimum cost.

enter image description here

How can I edit my query to get the result? Any help will be much appreciated!

Solution

  • First you need to get the Latest Date then you can find the minimum cost for them. e.g.

     select 
    a.OutletCode, 
    a.ProductID,
     LatestDate,
    MIN(Cost) AS MinPrice

from

    (
    SELECT MAX(CostingDate) AS LatestDate,
    OutletCode,
    ProductID  
    FROM AccountsCosting
    WHERE OutletCode = 'C&T01'
    GROUP BY OutletCode, ProductID
    ) a
    left join 
    FROM AccountsCosting b
    on
    a.OutletCode=b.OutletCode
    and a.ProductID=b.ProductID  
    and a.LatestDate=b.CostingDate

    group by a.OutletCode, a.ProductID, LatestDate