InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 09:36:53 GMT20http://www.sqlservercentral.com/Forums/Topic1357088-338-1.aspxTry This:-)select Id,MIN(a) from (select a.id, a.a from #MinAmt a ,#MinAmt union select a.id, a.b from #MinAmt a ,#MinAmtunion select a.id, a.c from #MinAmt a ,#MinAmt) a where a>0 group by IdThu, 13 Sep 2012 06:33:01 GMTraghavatgkhttp://www.sqlservercentral.com/Forums/Topic1357088-338-1.aspx[quote][b]dwain.c (9/13/2012)[/b][hr][quote][b]ChrisM@Work (9/13/2012)[/b][hr][code="sql"]SELECT Id, A, B, C, MinVal FROM #MinAmtCROSS APPLY ( SELECT MIN(a) FROM (VALUES (A),(B),(C) ) v (a) WHERE a > 0) x (MinVal) [/code];-)[/quote]And the referee throws down a red penalty flag!This is the SQL 2005 forum![/quote]Challenged! texpic always posts in the 2K8 forum!Thu, 13 Sep 2012 05:11:17 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1357088-338-1.aspx[quote][b]ChrisM@Work (9/13/2012)[/b][hr][code="sql"]SELECT Id, A, B, C, MinVal FROM #MinAmtCROSS APPLY ( SELECT MIN(a) FROM (VALUES (A),(B),(C) ) v (a) WHERE a > 0) x (MinVal) [/code];-)[/quote]And the referee throws down a red penalty flag!This is the SQL 2005 forum!Thu, 13 Sep 2012 04:55:38 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1357088-338-1.aspx[code="sql"]SELECT Id, A, B, C, MinVal FROM #MinAmtCROSS APPLY ( SELECT MIN(a) FROM (VALUES (A),(B),(C) ) v (a) WHERE a > 0) x (MinVal) [/code];-)Thu, 13 Sep 2012 02:31:22 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1357088-338-1.aspx[quote][b]texpic (9/12/2012)[/b][hr]If all three are zero, desired result would be zero.[/quote]What if all 3 are negative? Still zero?Wed, 12 Sep 2012 21:54:56 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1357088-338-1.aspxIf all three are zero, desired result would be zero.Wed, 12 Sep 2012 21:53:08 GMTtexpichttp://www.sqlservercentral.com/Forums/Topic1357088-338-1.aspxAh yes, NULLIF... Forgot about that one.That will work but we still need to hear from the OP their preferred return result for that case so it can be plugged into your version of my query.Wed, 12 Sep 2012 21:24:50 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1357088-338-1.aspx[quote][b]dwain.c (9/12/2012)[/b][hr][quote][b]Michael Valentine Jones (9/12/2012)[/b][hr][quote][b]dwain.c (9/12/2012)[/b][hr]Another way:[code="sql"];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[/code]BTW. You didn't specify what to return if all values are <= 0.[/quote]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.[/quote]That's why I asked what the OP wanted for that case. Easy enough to wrap another CASE around the whole result like:[code="sql"]CASE WHEN [longer case] IS 2147483647 THEN NULL ELSE [longer case] END[/code]Or if that seems too messy, put my CASE into a CROSS APPLY and just return that result to this latest CASE.[/quote]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:[code="sql"]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[/code]Wed, 12 Sep 2012 21:20:31 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1357088-338-1.aspx[quote][b]Michael Valentine Jones (9/12/2012)[/b][hr][quote][b]dwain.c (9/12/2012)[/b][hr]Another way:[code="sql"];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[/code]BTW. You didn't specify what to return if all values are <= 0.[/quote]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.[/quote]That's why I asked what the OP wanted for that case. Easy enough to wrap another CASE around the whole result like:[code="sql"]CASE WHEN [longer case] IS 2147483647 THEN NULL ELSE [longer case] END[/code]Or if that seems too messy, put my CASE into a CROSS APPLY and just return that result to this latest CASE.Wed, 12 Sep 2012 20:57:44 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1357088-338-1.aspx[quote][b]dwain.c (9/12/2012)[/b][hr]Another way:[code="sql"];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[/code]BTW. You didn't specify what to return if all values are <= 0.[/quote]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.Wed, 12 Sep 2012 20:53:36 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1357088-338-1.aspxAnother way:[code="sql"];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[/code]BTW. You didn't specify what to return if all values are <= 0.Wed, 12 Sep 2012 19:11:33 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1357088-338-1.aspx[quote][b]sogou34 (9/10/2012)[/b][hr]-------- http://www.chic-goods.us/ -----------hello,This is a wholesaler's web site.---blah blah[/quote]Reported.Tue, 11 Sep 2012 01:39:21 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1357088-338-1.aspxSS thanksMon, 10 Sep 2012 20:22:25 GMTtexpichttp://www.sqlservercentral.com/Forums/Topic1357088-338-1.aspx[code="sql"]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[/code]Results:[code="plain"]ID MinVal----------- -----------1 12 43 24 15 26 1(6 row(s) affected)[/code]Mon, 10 Sep 2012 20:19:39 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1357088-338-1.aspxYes 2, sorryMon, 10 Sep 2012 19:53:34 GMTtexpichttp://www.sqlservercentral.com/Forums/Topic1357088-338-1.aspxFor ID = 3, the answer should be 2 not 3, right?Mon, 10 Sep 2012 19:48:20 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic1357088-338-1.aspxThis 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[code="sql"]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 [/code]Mon, 10 Sep 2012 19:41:25 GMTtexpic