SQL Server - select min value from multiple columns

Asked By Mike Meathead on 11-Jun-07 01:16 PM
Hello,
I want to select the minimum(non zero) value over 3 fields.
EX.
 Col1    Col2   Col3
    23        0        12


I want to be able to select the value 12.
I was able to do this for the max value but not for min.
Any suggestions would be greatly appreciated.
Thanks

What was the syntax you used for max? - Robbe Morris replied to Mike Meathead on 11-Jun-07 02:24 PM

out of curiousity...

I did it 2 ways - Mike Meathead replied to Robbe Morris on 12-Jun-07 07:59 AM

The first way was with cases:
(select max(CASE WHEN c1a > c2a then
(case when c1a > c3a then c1a else c3a end)
 else
 (case when c2a > c3a then  c2a else c3a end)
  END
 ) as 'maxcopperadded' from engravingcylinderdata2

The second way was by a UDF:

CREATE FUNCTION maxvalue  (@col1 int ,  @col2 int , @col3 int ) 

returns int
as
begin
    declare @tablemax Table ( maxval  int )
    insert into @tablemax (maxval) values (@col1)
    insert into @tablemax(maxval) values (@col2)
    insert into @tablemax (maxval) values (@col3)
    return (select max(maxval) from @tablemax  where maxval > 0 and maxval is not null)
End

With the UDF I was able to find the mins also.......
M

First way is definitely faster - Robbe Morris replied to Mike Meathead on 12-Jun-07 09:07 AM

eop
I know but...... - Mike Meathead replied to Robbe Morris on 12-Jun-07 09:18 AM
I agree, but I could not figure out a case statement  to fine the minimum values over 3 columns. The problem is that some of the columns are "0" and I was always getting the "0" for the min and I wanted the non "0" value.  If you can figure out the logic for the min over 3 columns...Please let me know.
M
This code is a bit dirty - Robbe Morris replied to Mike Meathead on 12-Jun-07 09:43 AM

and could potentially be streamlined but I believe this will get what you are after in a UDF:

declare @returnValue int

select @returnValue = 0
 
-- Get max value first

 if (@colA > @returnValue)
 BEGIN
    select @returnValue = @colA
 END
 if (@colB > @returnValue)
 BEGIN
    select @returnValue = @colB
 END
 if (@colC > @returnValue)
 BEGIN
    select @returnValue = @colC
 END

-- Find minimum

if ((@colA > 0) and (@colA < @returnValue))
BEGIN
    select @returnValue = @colA
END
if ((@colB > 0) and (@colB < @returnValue))
BEGIN
    select @returnValue = @colB
END
if ((@colC > 0) and (@colC < @returnValue))
BEGIN
    select @returnValue = @colC
END

return @returnValue

Response - F Cali replied to Mike Meathead on 12-Jun-07 01:12 PM

One other way to do it using a UDF is to insert the values into a table and use the MIN aggregate function:

CREATE FUNCTION GetMinimum (@Int1 INT, @Int2 INT, @Int3 INT )

RETURNS INT

BEGIN

DECLARE @AllValues ( [Input] INT )

INSERT INTO @AllValues VALUES ( @Int1 )

INSERT INTO @AllValues VALUES ( @Int2 )

INSERT INTO @AllValues VALUES ( @Int3 )

RETURN (SELECT MIN([Input]) FROM @AllValues WHERE [Input] != 0)

END

SQL Server Helper
http://www.sql-server-helper.com/error-messages/msg-1-500.aspx

fdgfsdgf - hank lee replied to Mike Meathead on 21-May-08 01:25 PM
you need to normalize this table first. Load all thress columns data to a table like

colid   colvalue
1        23
2         0
3         12

or
select min (col)
from (
select col=col1 from table x
union all
select col=col2 from table x
uniion all
select col=col3 from table
)  z
select min value from multiple columns - Shailendrasinh Parmar replied to Mike Meathead on 19-Jul-08 09:13 AM
Why don't you try

select min(Col1), min(Col2), min(Col3) from table
where col1 > 0 and col2 > 0 and col3 > 0

This will solve your