## 22 replies

Last post May 11, 2012 02:33 PM by sandeepmittal11

Member

303 Points

251 Posts

### select min on the base of condition

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

Star

9184 Points

1570 Posts

### Re: select min on the base of condition

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();

• Edited by AidyF on May 11, 2012 12:34 PM

Member

303 Points

251 Posts

### Re: select min on the base of condition

i want sql query not linq............

(where is yogi the great.......)

Star

14460 Points

2402 Posts

### Re: select min on the base of condition

#### vish02chouhan

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?

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

Member

303 Points

251 Posts

### Re: select min on the base of condition

from a single column

Star

14460 Points

2402 Posts

### Re: select min on the base of condition

--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

Star

8597 Points

1841 Posts

### Re: select min on the base of condition

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)

Participant

1754 Points

340 Posts

### Re: select min on the base of condition

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:

select MIN(CASE WHEN MyColumn <> 0 then MyColumn end) as MyMin
from MyTable

Hope this helps.

• Edited by silvioyf on May 11, 2012 12:44 PM

Star

9184 Points

1570 Posts

### Re: select min on the base of condition

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

Member

303 Points

251 Posts