CODEWITHSUNDEEP
sqlsql-server
Jerry
Jerry

Reputation: 11

find the least of 3 values NOT including zero

I am looking for the least of three columns (Market, LiFo, Wgtd_Avg). This I can do, but exclude any of the three that are equal to zero.
I only want to find the least of those > zero. 

SELECT Market, LiFo, Wgtd_Avg,
(
    CASE
        WHEN Market < LiFo AND Market < Wgtd_Avg AND Market > 0 THEN Market
        WHEN LiFo  < Wgtd_Avg AND LiFo > 0 THEN LiFo
        WHEN Wgtd_Avg > 0 THEN Wgtd_Avg
        ELSE 0
    END
) AS LeastOfThree
FROM VF_CasINV_Cost

Upvotes: 1

Views: 332

Answers (2)

Pavling
Pavling

Reputation: 3963

Original answer:

SELECT min(Value) FROM
(
SELECT Market as Value FROM VF_CasINV_Cost WHERE Market > 0
UNION
SELECT Wgtd_Avg as Value FROM VF_CasINV_Cost WHERE Wgtd_Avg > 0
UNION
SELECT LiFo as Value FROM VF_CasINV_Cost WHERE LiFo > 0
) union_table

Edited after comments:

SELECT Market, Wgtd_Avg, LiFo, min(least) AS leastOfThree FROM
(
SELECT min(market) as market, min(Market) as least FROM VF_CasINV_Cost WHERE Market > 0
UNION
SELECT min(Wgtd_avg) as Wgtd_Avg, min(Wgtd_Avg) as least FROM VF_CasINV_Cost WHERE Wgtd_Avg > 0
UNION
SELECT min(lifo) as LiFo, min(lifo) as least FROM VF_CasINV_Cost WHERE LiFo > 0
) as union_table

I'm away from a computer with a sql sever, so can't test this at moment, but it should be close to working... I'll test it and edit as soon as I'm back.

Upvotes: 2

Andriy M
Andriy M

Reputation: 77667

SELECT
  MIN(
    CASE x.f
      WHEN 1 THEN t.Market
      WHEN 2 THEN t.Wgtd_Avg
      WHEN 3 THEN t.LiFo
    END
  ) AS MinValue
FROM VF_Cas_INV_Cost t
  INNER JOIN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) x (f)
    ON CASE x.f
      WHEN 1 THEN t.Market
      WHEN 2 THEN t.Wgtd_Avg
      WHEN 3 THEN t.LiFo
    END > 0

Upvotes: 1

Related Questions