CODEWITHSUNDEEP
sqlmaxmin
Aung Kaung Hein
Aung Kaung Hein

Reputation: 1566

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!

Upvotes: 3

Views: 51980

Answers (5)

Nidhi
Nidhi

Reputation: 11

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

Upvotes: 1

SinisterPenguin
SinisterPenguin

Reputation: 1618

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

Upvotes: 1

Luis LL
Luis LL

Reputation: 2993

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

Upvotes: 1

DennisH
DennisH

Reputation: 383

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;

Upvotes: 1

nhrobin
nhrobin

Reputation: 903

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

Upvotes: 4

Related Questions