## Minimum Value 3 Fields > 0

 Author Message texpic SSCrazy Group: General Forum Members Points: 2291 Visits: 410 This seems like it should be easy but I can't seem to come up with a simple formula. Need the minimum value where answer > 0.Result:1 = 12 = 43 = 34 = 15 = 26 = 1`CREATE TABLE #MinAmt(Id int, A int, B int, C int)INSERT INTO #MinAmt(Id,A,B,C)SELECT 1,1,2,3 UNION ALLSELECT 2,4,5,6 UNION ALLSELECT 3,0,2,3 UNION ALLSELECT 4,1,2,0 UNION ALLSELECT 5,-1,2,3 UNION ALLSELECT 6,1,2,-3 ` ColdCoffee SSCoach Group: General Forum Members Points: 15873 Visits: 5555 For ID = 3, the answer should be 2 not 3, right? texpic SSCrazy Group: General Forum Members Points: 2291 Visits: 410 Yes 2, sorry Michael Valentine Jones One Orange Chip Group: General Forum Members Points: 26810 Visits: 11930 `select ID, [MinVal] = ( select min(x) from ( select x=A where A > 0 union all select x=B where B > 0 union all select x=C where C > 0 ) m )from #MinAmtorder by ID`Results:`ID MinVal----------- -----------1 12 43 24 15 26 1(6 row(s) affected)` texpic SSCrazy Group: General Forum Members Points: 2291 Visits: 410 SS thanks Phil Parkin SSC Guru Group: General Forum Members Points: 97341 Visits: 21996 sogou34 (9/10/2012)-------- http://www.chic-goods.us/ -----------hello,This is a wholesaler's web site.---blah blahReported. Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot. dwain.c SSC-Dedicated Group: General Forum Members Points: 32139 Visits: 6431 Another way:`;WITH MyValues AS ( SELECT Id ,A=CASE WHEN A > 0 THEN A ELSE 2147483647 END ,B=CASE WHEN B > 0 THEN B ELSE 2147483647 END ,C=CASE WHEN C > 0 THEN C ELSE 2147483647 END FROM #MinAmt)SELECT Id ,MinAmt=CASE WHEN A < B THEN CASE WHEN A < C THEN A ELSE C END WHEN B < A THEN CASE WHEN B < C THEN B ELSE C END ELSE CASE WHEN C < A THEN C ELSE A END ENDFROM MyValues`BTW. You didn't specify what to return if all values are <= 0. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables Michael Valentine Jones One Orange Chip Group: General Forum Members Points: 26810 Visits: 11930 dwain.c (9/12/2012)Another way:`;WITH MyValues AS ( SELECT Id ,A=CASE WHEN A > 0 THEN A ELSE 2147483647 END ,B=CASE WHEN B > 0 THEN B ELSE 2147483647 END ,C=CASE WHEN C > 0 THEN C ELSE 2147483647 END FROM #MinAmt)SELECT Id ,MinAmt=CASE WHEN A < B THEN CASE WHEN A < C THEN A ELSE C END WHEN B < A THEN CASE WHEN B < C THEN B ELSE C END ELSE CASE WHEN C < A THEN C ELSE A END ENDFROM MyValues`BTW. You didn't specify what to return if all values are <= 0.You also need to deal with the case where the values are null.You code returns 2147483647 when they are all <1 or null, and I doubt that is what they would want.Returning a null probably makes the most sense for that case. dwain.c SSC-Dedicated Group: General Forum Members Points: 32139 Visits: 6431 Michael Valentine Jones (9/12/2012)dwain.c (9/12/2012)Another way:`;WITH MyValues AS ( SELECT Id ,A=CASE WHEN A > 0 THEN A ELSE 2147483647 END ,B=CASE WHEN B > 0 THEN B ELSE 2147483647 END ,C=CASE WHEN C > 0 THEN C ELSE 2147483647 END FROM #MinAmt)SELECT Id ,MinAmt=CASE WHEN A < B THEN CASE WHEN A < C THEN A ELSE C END WHEN B < A THEN CASE WHEN B < C THEN B ELSE C END ELSE CASE WHEN C < A THEN C ELSE A END ENDFROM MyValues`BTW. You didn't specify what to return if all values are <= 0.You also need to deal with the case where the values are null.You code returns 2147483647 when they are all <1 or null, and I doubt that is what they would want.Returning a null probably makes the most sense for that case.That's why I asked what the OP wanted for that case. Easy enough to wrap another CASE around the whole result like:`CASE WHEN [longer case] IS 2147483647 THEN NULL ELSE [longer case] END`Or if that seems too messy, put my CASE into a CROSS APPLY and just return that result to this latest CASE. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables Michael Valentine Jones One Orange Chip Group: General Forum Members Points: 26810 Visits: 11930 dwain.c (9/12/2012)Michael Valentine Jones (9/12/2012)dwain.c (9/12/2012)Another way:`;WITH MyValues AS ( SELECT Id ,A=CASE WHEN A > 0 THEN A ELSE 2147483647 END ,B=CASE WHEN B > 0 THEN B ELSE 2147483647 END ,C=CASE WHEN C > 0 THEN C ELSE 2147483647 END FROM #MinAmt)SELECT Id ,MinAmt=CASE WHEN A < B THEN CASE WHEN A < C THEN A ELSE C END WHEN B < A THEN CASE WHEN B < C THEN B ELSE C END ELSE CASE WHEN C < A THEN C ELSE A END ENDFROM MyValues`BTW. You didn't specify what to return if all values are <= 0.You also need to deal with the case where the values are null.You code returns 2147483647 when they are all <1 or null, and I doubt that is what they would want.Returning a null probably makes the most sense for that case.That's why I asked what the OP wanted for that case. Easy enough to wrap another CASE around the whole result like:`CASE WHEN [longer case] IS 2147483647 THEN NULL ELSE [longer case] END`Or if that seems too messy, put my CASE into a CROSS APPLY and just return that result to this latest CASE.Wrapping it in NULLIF([longer case],2147483647) would be simpler.Of course, if one of the values in the table was actually 2147483647, it would return a null, so you might need extra code to deal with that:`WITH MyValues AS ( SELECT Id ,A=CASE WHEN A > 0 THEN A ELSE 3000000000000 END ,B=CASE WHEN B > 0 THEN B ELSE 3000000000000 END ,C=CASE WHEN C > 0 THEN C ELSE 3000000000000 END FROM #MinAmt)SELECT Id ,MinAmt= convert(int,nullif( CASE WHEN A < B THEN CASE WHEN A < C THEN A ELSE C END WHEN B < A THEN CASE WHEN B < C THEN B ELSE C END ELSE CASE WHEN C < A THEN C ELSE A END END ,3000000000000))FROM MyValues`