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

Question

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!

Solution 1

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

Solution 2

if you are using SQL Server 2005 and after you can use CTE and RANK() OVER()

;WITH AccountCostingForOutletCode AS  (
    SELECT OutletCode, ProductID, CostingDate, Cost, 
       RANK() OVER( PARTITION BY OutletCode, ProductID ORDER BY Cost, CostingDate DESC) AS Rnk
    FROM AccountsCosting
    WHERE OutletCode = 'C&T01'
)
SELECT TOP 1 OutletCode, ProductID, CostingDate, Cost, Rnk
FROM AccountCostingForOutletCode
ORDER BY Rnk

Solution 3

I think you will need to do 2 queries for the MAX & MIN, then Join them together to get your single row. I'd either use temp tables or CTE's, e.g.

WITH CTE_Max AS
(SELECT MAX(costingDate) as latestDate,
 Oultletcode,
ProductID
FROM AccountsCosting
GROUP BY OutletCode, ProductID),

CTE_Min AS
(SELECT MIN(cost) as MinPrice,
Oultletcode,
ProductID
FROM AccountsCosting
GROUP BY OutletCode, ProductID)

SELECT * FROM CTE_Max 
JOIN CTE_Min
ON CTE_Max.outletCode=CTE_Min.outletCode
AND CTE_Max.productID=CTE_Min.productID

Solution 4

You can use something if you only wish to show output

SELECT ProductName,Max(price) as MxP, '-' as minP from products 
UNION
SELECT ProductName,'-' as MxP , Min(price) as minP from products

Solution 5

If i understand you correctly, you want the minimum cost for the latest date?!

Try this:

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

View more solutions

Comments

[Aung Kaung Hein]

This is exactly what I wanted. It was really difficult to explain what I want to do without showing the query that I have. Thank you so much for your help.

[Aung Kaung Hein]

It returned only one row with latest costing date and cost among all the records in my table because of using IN in WHERE statement. Anyway, I really appreciate your help! +1 for your answer.