## 22 replies

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

Member

303 Points

251 Posts

### select minimum value

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

5514 Points

810 Posts

### Re: select minimum value

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

Hope this helps...

Participant

778 Points

174 Posts

### Re: select minimum value

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

34535 Points

5554 Posts

### Re: select minimum value

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

My Site

Star

14460 Points

2402 Posts

### Re: select minimum value

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

Star

14460 Points

2402 Posts

### Re: select minimum value

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

Member

303 Points

251 Posts

### Re: select minimum value

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

14460 Points

2402 Posts

### Re: select minimum value

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

Contributor

6801 Points

1059 Posts

### Re: select minimum value

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

117340 Points

8005 Posts

Moderator

MVP

### Re: select minimum value

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