Minimum Value 3 Fields > 0

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 13, 2012 at 5:11 am

  #1537132

  dwain.c (9/13/2012)

  ---

  ChrisM@Work (9/13/2012)

  ---

  SELECT

  Id, A, B, C,

  MinVal

  FROM #MinAmt

  CROSS APPLY (

  SELECT MIN(a)

  FROM (VALUES (A),(B),(C) ) v (a)

  WHERE a > 0

  ) x (MinVal)

  

  And the referee throws down a red penalty flag!

  This is the SQL 2005 forum!

  Challenged! texpic always posts in the 2K8 forum!

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• raghava_tg

  Ten Centuries

  Points: 1014

  More actions

  September 13, 2012 at 6:33 am

  #1537161

  Try This

  

  select Id,MIN(a) from (

  select a.id, a.a from #MinAmt a ,#MinAmt

  union

  select a.id, a.b from #MinAmt a ,#MinAmt

  union

  select a.id, a.c from #MinAmt a ,#MinAmt

  ) a where a>0 group by Id