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

max, min, sql

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

Problem

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: E.g - for productID: `200006` ``` SELECT * FROM AccountsCosting WHERE ProductID = 200006 AND OutletCode = 'C&T01' ORDER BY CostingDate DESC ``` 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. How can I edit my query to get the result? Any help will be much appreciated!

Original source