22 replies

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

Member

115 Points

262 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

4392 Points

933 Posts

Re: select minimum value

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

Hope this helps...

Member

450 Points

173 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

27228 Points

6185 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

9966 Points

2300 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

9966 Points

2300 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

115 Points

262 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

9966 Points

2300 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

5274 Points

1083 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

119327 Points

8999 Posts

Moderator

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

*/```

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

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

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

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

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

Star

9966 Points

2300 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

115 Points

262 Posts

Re: select min on the base of condition

from a single column

Star

9966 Points

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

Contributor

5031 Points

1830 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

1070 Points

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

All-Star

37241 Points

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

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

@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

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

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

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

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

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