int[] data = new int[] {10, 3, 0, 8, 4, 0, 4, 2 };
//int[] data = new int[] { 0, 0, 0 };
int min = 0;
if (data.Any(d => d != 0))
min = data.Where(d => d != 0).Min();
Or
int[] data = new int[] {10, 3, 0, 8, 4, 0, 4, 2 };
//int[] data = new int[] { 0, 0, 0 };
int min = 0;
IEnumerable<int> nonZero = data.Where(d => d != 0);
if (nonZero.Any())
min = nonZero.Min();
I want to select min from all columns,but min is not 0 if any other columns has non zero values,but if all the column has 0 value then select 0
Can you show us your table's columns?
Without knowing total number of column no one can help you.
check the below query for finding minimum value from 3 columns,
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
--suppose your table contain 5 columns
declare @table table
(
id int IDENTITY(1,1),
m1 decimal(10,4),
m2 decimal(10,4),
m3 decimal(10,4),
m4 decimal(10,4),
m5 decimal(10,4)
)
INSERT INTO @table
SELECT 13,2,0,3,41 UNION ALL
SELECT 1,0,0,3,1 UNION ALL
SELECT 8,2,9,3,10 UNION ALL
SELECT 0,0,0,0,0 UNION ALL
SELECT 0,7,12,0,14
;with mycte as
(
select id, col, val
from
(
SELECT id, m1, m2, m3,m4,m5
From @table
) src
unpivot
(
val for col in ([m1],[m2],[m3],[m4],[m5])
) unpvt
)
SELECT a.id,
ISNULL(b.val,0) minVal
from @table a left join
(
select id, MIN(val) val from mycte
WHERE val>0
group by id
) b on a.id=b.id
--output
--id minVal
--1 2.0000
--2 1.0000
--3 2.0000
--4 0.0000
--5 7.0000
--enchanment
--if your table contain more columns than add column to select list
--as well as into unpivot list, than you are able to find the minimum value
declare @count int
declare @countAll int
select @count = COUNT(ColName) from NumTable WHERE ColName = 0
select @countAll = COUNT(ColName) from NumTable
IF @count = @countAll
SELECT 0
ELSE
SELECT MIN(ColName) FROM NumTable WHERE ColName <> 0
vish02chouha...
Member
303 Points
251 Posts
select min on the base of condition
May 11, 2012 12:26 PM|LINK
I want to select min from all columns,but min is not 0 if any other columns has non zero values,but if all the column has 0 value then select 0
AidyF
Star
9204 Points
1570 Posts
Re: select min on the base of condition
May 11, 2012 12:33 PM|LINK
int[] data = new int[] {10, 3, 0, 8, 4, 0, 4, 2 }; //int[] data = new int[] { 0, 0, 0 }; int min = 0; if (data.Any(d => d != 0)) min = data.Where(d => d != 0).Min();Or
int[] data = new int[] {10, 3, 0, 8, 4, 0, 4, 2 }; //int[] data = new int[] { 0, 0, 0 }; int min = 0; IEnumerable<int> nonZero = data.Where(d => d != 0); if (nonZero.Any()) min = nonZero.Min();vish02chouha...
Member
303 Points
251 Posts
Re: select min on the base of condition
May 11, 2012 12:33 PM|LINK
i want sql query not linq............
(where is yogi the great.......)
yrb.yogi
Star
14460 Points
2402 Posts
Re: select min on the base of condition
May 11, 2012 12:36 PM|LINK
Can you show us your table's columns?
Without knowing total number of column no one can help you.
check the below query for finding minimum value from 3 columns,
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.Net All About
vish02chouha...
Member
303 Points
251 Posts
Re: select min on the base of condition
May 11, 2012 12:37 PM|LINK
from a single column
yrb.yogi
Star
14460 Points
2402 Posts
Re: select min on the base of condition
May 11, 2012 12:40 PM|LINK
--suppose your table contain 5 columns declare @table table ( id int IDENTITY(1,1), m1 decimal(10,4), m2 decimal(10,4), m3 decimal(10,4), m4 decimal(10,4), m5 decimal(10,4) ) INSERT INTO @table SELECT 13,2,0,3,41 UNION ALL SELECT 1,0,0,3,1 UNION ALL SELECT 8,2,9,3,10 UNION ALL SELECT 0,0,0,0,0 UNION ALL SELECT 0,7,12,0,14 ;with mycte as ( select id, col, val from ( SELECT id, m1, m2, m3,m4,m5 From @table ) src unpivot ( val for col in ([m1],[m2],[m3],[m4],[m5]) ) unpvt ) SELECT a.id, ISNULL(b.val,0) minVal from @table a left join ( select id, MIN(val) val from mycte WHERE val>0 group by id ) b on a.id=b.id --output --id minVal --1 2.0000 --2 1.0000 --3 2.0000 --4 0.0000 --5 7.0000 --enchanment --if your table contain more columns than add column to select list --as well as into unpivot list, than you are able to find the minimum value.Net All About
christiandev
Star
8597 Points
1841 Posts
Re: select min on the base of condition
May 11, 2012 12:41 PM|LINK
DECLARE @number int
set @number = (SELECT MIN(column) where column > 0)
IF (@number is null)
SET @number = 0
select @number
try something like that
Regards, Christiandev (@chrisdev80), MCPD Web (2 & 4) & MCTS Windows (www.ScoreDonkey.com)
silvioyf
Participant
1754 Points
340 Posts
Re: select min on the base of condition
May 11, 2012 12:41 PM|LINK
Do not quite understand your question ...
If the value of a column in all the lines is equal to zero, the MIN will also be zero.
edit: maybe this way:
Hope this helps.
AidyF
Star
9204 Points
1570 Posts
Re: select min on the base of condition
May 11, 2012 12:49 PM|LINK
vish02chouha...
Member
303 Points
251 Posts
Re: select min on the base of condition
May 11, 2012 01:05 PM|LINK
@silvioyf
it works fine,but when all the column value is 0 then the results come null ,i want 0 in that condition