# select minimum value [Answered]RSS

## 22 replies

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

Member

115 Points

262 Posts

### select minimum value

May 01, 2012 10:31 AM|vish02chouhan|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

Contributor

4392 Points

933 Posts

### Re: select minimum value

May 01, 2012 10:39 AM|Vipindas|LINK

```select
min(Price)
from Table1
Where Price > 0```

Hope this helps...

Member

450 Points

173 Posts

### Re: select minimum value

May 01, 2012 10:42 AM|Shatrughna.kumar|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```

All-Star

27228 Points

6185 Posts

### Re: select minimum value

May 01, 2012 10:47 AM|kedarrkulkarni|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

Star

9966 Points

2300 Posts

### Re: select minimum value

May 01, 2012 10:48 AM|yrb.yogi|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

Star

9966 Points

2300 Posts

### Re: select minimum value

May 01, 2012 10:53 AM|yrb.yogi|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

Member

115 Points

262 Posts

### Re: select minimum value

May 01, 2012 11:05 AM|vish02chouhan|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

Star

9966 Points

2300 Posts

### Re: select minimum value

May 01, 2012 11:07 AM|yrb.yogi|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

Contributor

5274 Points

1083 Posts

### Re: select minimum value

May 01, 2012 11:28 AM|sandeepmittal11|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

• ### limno

All-Star

119317 Points

8999 Posts

Moderator

### Re: select minimum value

May 01, 2012 01:23 PM|limno|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

*/```

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm

Member

115 Points

262 Posts

### select min on the base of condition

May 11, 2012 12:26 PM|vish02chouhan|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

All-Star

37241 Points

9081 Posts

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

May 11, 2012 12:33 PM|AidyF|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();```

Please mark as answer the first person to give the solution, and if someone just posts a link to the solution, they didn't solve your problem - they're just better at Google :)

Member

115 Points

262 Posts

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

May 11, 2012 12:33 PM|vish02chouhan|LINK

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

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

Star

9966 Points

2300 Posts

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

May 11, 2012 12:36 PM|yrb.yogi|LINK

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

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

Yogesh Bhadauriya
.Net All About

Member

115 Points

262 Posts

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

May 11, 2012 12:37 PM|vish02chouhan|LINK

from a single column

Star

9966 Points

2300 Posts

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

May 11, 2012 12:40 PM|yrb.yogi|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```

Yogesh Bhadauriya
.Net All About

Contributor

5031 Points

1830 Posts

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

May 11, 2012 12:41 PM|christiandev|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

Please remember to Mark As Answer if helpful

Regards, Christiandev (@chrisdev80), MCPD Web (2 & 4) & MCTS Windows (www.ScoreDonkey.com)

Participant

1070 Points

335 Posts

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

May 11, 2012 12:41 PM|silvioyf|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:

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

Hope this helps.

All-Star

37241 Points

9081 Posts

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

May 11, 2012 12:49 PM|AidyF|LINK

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

Please mark as answer the first person to give the solution, and if someone just posts a link to the solution, they didn't solve your problem - they're just better at Google :)

Member

115 Points

262 Posts

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

May 11, 2012 01:05 PM|vish02chouhan|LINK

### @silvioyf

it works fine,but when all the column value is 0 then the results come null ,i want 0 in that condition

Contributor

5031 Points

1830 Posts

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

May 11, 2012 01:09 PM|christiandev|LINK

use the code I gave, that checks if its null, and returns 0 if so....

Please remember to Mark As Answer if helpful

Regards, Christiandev (@chrisdev80), MCPD Web (2 & 4) & MCTS Windows (www.ScoreDonkey.com)

Contributor

5031 Points

1830 Posts

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

May 11, 2012 01:10 PM|christiandev|LINK

..or, if you want to use case, then add an ELSE in there which returns 0.

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

Please remember to Mark As Answer if helpful

Regards, Christiandev (@chrisdev80), MCPD Web (2 & 4) & MCTS Windows (www.ScoreDonkey.com)

Contributor

5274 Points

1083 Posts

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

May 11, 2012 02:33 PM|sandeepmittal11|LINK

```/*
CREATE FUNCTION Split(
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
INSERT INTO @t(val)
SELECT  r.value('.','varchar(MAX)') as item
FROM  @xml.nodes('/t') as records(r)
RETURN
END

CREATE FUNCTION dbo.fnMinVal(@val varchar(max))
RETURNS NUMERIC(16,2)
AS
BEGIN
DECLARE @retval NUMERIC(16,2)
SELECT @retval = MIN(val)
FROM dbo.Split(@val, ',')
WHERE cast(val as NUMERIC(16,2))>0
RETURN ISNULL(@retval,0)
END
*/
DECLARE @tab TABLE(
amt1 NUMERIC(16,2), amt2 NUMERIC(16,2), amt3 NUMERIC(16,2)
)

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(cast(amt1 as varchar)
+ ',' + cast(amt2 as varchar)
+ ',' + cast(amt3 as varchar)
) as minval
FROM @tab```

In the query, just concatenate as many cols like doing for 3 cols

Sandeep Mittal | My Blog - IT Developer Zone