select minimum value RSS

• • Reply
  

  vish02chouha...

  Member

  303 Points

  251 Posts

  select minimum value

  May 01, 2012 10:31 AM|LINK

  i have a table in which there are three columns of money type,i want to select minimum value from three but not 0 means if any one column or two columns of a row containing the 0 the we select minimum from non 0 containing column if all the three columns contain 0 then we select 0

• • Reply
  

  Vipindas

  Contributor

  5514 Points

  810 Posts

  Re: select minimum value

  May 01, 2012 10:39 AM|LINK

  select
  min(Price)
  from Table1
  Where Price > 0

  Hope this helps...

  Please remember to mark as answer if it helps you
• • Reply
  

  Shatrughna.k...

  Participant

  778 Points

  174 Posts

  Re: select minimum value

  May 01, 2012 10:42 AM|LINK

  What about this:
  
  

  DECLARE @Table TABLE
  (
  	C1 INT,
  	C2 INT,
  	C3 INT
  )
  
  INSERT INTO @Table
  SELECT 1,2,0 UNION ALL
  SELECT 1,0,0 UNION ALL
  SELECT 1,4,5 UNION ALL
  SELECT 1,5,5 UNION ALL
  SELECT 0,0,0
  
  SELECT
  CASE
  WHEN C1 <= C2 AND C1 <= C3 AND C1!=0 THEN C1
  WHEN C2 <= C1 AND C2 <= C3 AND C2!=0 THEN C2
  WHEN C3 <= C1 AND C3 <= C2 AND C3!=0 THEN C3 ELSE 0 END FROM @Table

  • Edited by Shatrughna.kumar on May 01, 2012 10:49 AM
• • Reply
  

  kedarrkulkar...

  All-Star

  34013 Points

  5468 Posts

  Re: select minimum value

  May 01, 2012 10:47 AM|LINK

  Vipindas

  select  min(Price)  from Table1  Where Price > 0

  Hope this helps...

  this will give minimum value of price column.. vish02chouhan wants to get minimum value from 3 columns of same row

  try this

  select
  case when column_1 < column_2 and column_1 < column_3 and column_1 <> 0 then column_1
       when column_2 < column_1 and column_2 < column_3 and column_2 <> 0 then column_2
       when column_3 < column_1 and column_3 < column_2 and column_3 <> 0 then column_3
       else column_1
  end as ColumnName
  from TableName
  where ...

  hope this helps...

  Cheers!
  KK
  
  Please mark as Answer if post helps in resolving your issue
  My Site
• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: select minimum value

  May 01, 2012 10:48 AM|LINK

  DECLARE @TABLE Table
  (
  	MoneyColumnName1 MONEY,MoneyColumnName2 MONEY,MoneyColumnName3 MONEY
  )
  INSERT INTO @TABLE
  SELECT 0,0,0 UNION ALL
  SELECT 10,12,0 UNION ALL
  SELECT 0,13,15 UNION ALL
  SELECT 13,15,18
  
  SELECT
  CASE WHEN MoneyColumnName1=0 AND MoneyColumnName2=0 AND MoneyColumnName3=0
  	THEN 0
  WHEN MoneyColumnName1=0
  	THEN
  		CASE WHEN MoneyColumnName2 < MoneyColumnName3
  			Then MoneyColumnName2 ELSE MoneyColumnName3 END
  WHEN MoneyColumnName2=0
  	THEN
  	CASE WHEN MoneyColumnName1 < MoneyColumnName3
  			Then MoneyColumnName1 ELSE MoneyColumnName3 END
  WHEN MoneyColumnName3=0
  	THEN
  	CASE WHEN MoneyColumnName1 < MoneyColumnName2
  			Then MoneyColumnName1 ELSE MoneyColumnName2 END
  WHEN MoneyColumnName1 < MoneyColumnName2 And MoneyColumnName1 < MoneyColumnName3 Then MoneyColumnName1
              When MoneyColumnName2 < MoneyColumnName1 And MoneyColumnName2 < MoneyColumnName3 Then MoneyColumnName2
              Else MoneyColumnName3
  End As TheMin
  FROM @TABLE
  
  --output
  --TheMin
  --0.00
  --10.00
  --13.00
  --13.00

  Yogesh Bhadauriya
  .Net All About
  
  • Marked as answer by vish02chouhan on May 01, 2012 12:12 PM
• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: select minimum value

  May 01, 2012 10:53 AM|LINK

  Try below code. I have tested with multiple output...

  DECLARE @Table TABLE
  (
          C1 INT,
          C2 INT,
          C3 INT
  )
  
  INSERT INTO @Table
  SELECT 1,2,0 UNION ALL
  SELECT 1,0,0 UNION ALL
  SELECT 8,4,1 UNION ALL
  SELECT 9,5,3 UNION ALL
  SELECT 0,0,0
  
  SELECT
  CASE WHEN c1=0 AND c2=0 AND c3=0
  	THEN 0
  WHEN c1=0 AND C2=0
  	THEN C3
  WHEN c2=0 AND C3=0
  	THEN C1
  WHEN c1=0 AND C3=0
  	THEN C2
  WHEN c1=0
  	THEN CASE WHEN c2 < c3 Then c2 ELSE c3 END
  WHEN c2=0
  	THEN CASE WHEN c1 < c3 Then c1 ELSE c3 END
  WHEN c3=0
  	THEN CASE WHEN c1 < c2 Then c1 ELSE c2 END
  WHEN c1 < c2 And c1 < c3 Then c1
              When c2 < c1 And c2 < c3 Then c2
              Else c3
  End As TheMin
  FROM @TABLE
  
  --output
  --TheMin
  --1
  --1
  --1
  --3
  --0

  
  
  

  Yogesh Bhadauriya
  .Net All About
  
  • Marked as answer by vish02chouhan on May 01, 2012 12:12 PM
• • Reply
  

  vish02chouha...

  Member

  303 Points

  251 Posts

  Re: select minimum value

  May 01, 2012 11:05 AM|LINK

  your solution is not working it gives 0 

  1,2,0
   1,0,0

  when Ichange

  DECLARE @Table TABLE
  (
  C1 INT,
  C2 INT,
  C3 INT
  )

  INSERT INTO @Table
  SELECT 1,2,0 UNION ALL
  SELECT 1,0,0 UNION ALL
  SELECT 1,4,5 UNION ALL
  SELECT 1,5,5 UNION ALL
  SELECT 0,0,0
  select * from @Table
  SELECT
  CASE
  WHEN C1 <= C2 AND C1 <= C3 AND C1!=0 THEN C1
  WHEN C2 <= C1 AND C2 <= C3 AND C2!=0 THEN C2
  WHEN C3 <= C1 AND C3 <= C2 AND C3!=0 THEN C3 ELSE 0 END FROM @Table

• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: select minimum value

  May 01, 2012 11:07 AM|LINK

  vish02chouhan

  your solution is not working it gives 0 

  You must see this solution.

  DECLARE @Table TABLE
  (
          C1 INT,
          C2 INT,
          C3 INT
  )
  
  INSERT INTO @Table
  SELECT 1,2,0 UNION ALL
  SELECT 1,0,0 UNION ALL
  SELECT 8,4,1 UNION ALL
  SELECT 9,5,3 UNION ALL
  SELECT 0,0,0
  
  SELECT
  CASE WHEN c1=0 AND c2=0 AND c3=0
          THEN 0
  WHEN c1=0 AND C2=0
          THEN C3
  WHEN c2=0 AND C3=0
          THEN C1
  WHEN c1=0 AND C3=0
          THEN C2
  WHEN c1=0
          THEN CASE WHEN c2 < c3 Then c2 ELSE c3 END
  WHEN c2=0
          THEN CASE WHEN c1 < c3 Then c1 ELSE c3 END
  WHEN c3=0
          THEN CASE WHEN c1 < c2 Then c1 ELSE c2 END
  WHEN c1 < c2 And c1 < c3 Then c1
              When c2 < c1 And c2 < c3 Then c2
              Else c3
  End As TheMin
  FROM @TABLE
  
  --output
  --TheMin
  --1
  --1
  --1
  --3
  --0

  
  
  

  Yogesh Bhadauriya
  .Net All About
  
• • Reply
  

  sandeepmitta...

  Contributor

  6767 Points

  1057 Posts

  Re: select minimum value

  May 01, 2012 11:28 AM|LINK

  /*
  create function dbo.fnMinVal(@val1 money, @val2 money, @val3 money)
  returns money
  as
  begin
  	declare @retval money
  	;with cte as(
  		select @val1 as val
  		union all select @val2
  		union all select @val3
  	)
  	select @retval = min(val) from cte where val>0
      return isnull(@retval,0)
  end
  */
  DECLARE @tab TABLE(
     amt1 money, amt2 money, amt3 money
  )
  
  INSERT INTO @tab
  SELECT 1,2,3
  UNION ALL SELECT 0,2,3
  UNION ALL  SELECT 1,2,0
  UNION ALL SELECT 4,2,5
  
  SELECT dbo.fnMinVal(amt1, amt2, amt3)
  FROM @tab

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Edited by sandeepmittal11 on May 01, 2012 12:26 PM
  • Edited by sandeepmittal11 on May 01, 2012 12:27 PM
  • Edited by sandeepmittal11 on May 01, 2012 12:28 PM
• • Reply
  

  limno

  All-Star

  117314 Points

  7997 Posts

  Moderator

  MVP

  Re: select minimum value

  May 01, 2012 01:23 PM|LINK

  IF OBJECT_ID('tempdb..#myTestTable','U') IS NOT NULL
          DROP TABLE #myTestTable
  
  create TABLE #myTestTable
  ( id int IDENTITY(1,1),
          m1 decimal(10,4),
          m2 decimal(10,4),
          m3 decimal(10,4)
  )
   
  INSERT INTO #myTestTable values (5.50,3.32,2.22),(5.50,0,2.22),(0,0,0),(0,3.32,2.22)
  
  
  ;with mycte
  as
  (select id, col, val from
  (SELECT    id, m1, m2, m3 From #myTestTable) src
   unpivot (val for col in ([m1],[m2],[m3])) unpvt
   )
   SELECT a.id, ISNULL(b.val,0) minVal from #myTestTable a left join (
   select id, MIN(val) val from mycte
   WHERE val>0
   group by id) b on a.id=b.id
   /*
   1 2.2200
  2 2.2200
  3 0.0000
  4 2.2200
   
   */

  Limno
  
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
  • Marked as answer by vish02chouhan on May 08, 2012 04:01 AM