MSSQL MAX, MIN from the column data

How to compare and find the MAX, MIN from the column data?
Some time we want to find the MAX, MIN form our own data or Colunm data On that case MSSQL dose not have system function to do it but here is the solution to do it.

See the example to find Max value:

-- select dbo.FuncGetMax(123,5,null,null,null)


ALTER FUNCTION [dbo].[FuncGetMax] (@str float, @str1 float, @str2 float, @str3 float,@str4 float)
RETURNS float
BEGIN

Declare @result int

-- First compare
if @str > @str1
set @result=@str
else
set @result=@str1

-- 2nd compare
if @str2 is null OR @result > @str2
set @result=@result
else
set @result=@str2

-- 3rd compare
if @str3 is null OR @result > @str3
set @result=@result
else
set @result=@str3

-- 4th compare
if @str4 is null OR @result > @str4
set @result=@result
else
set @result=@str4

return @result

end

See the example to find MIN value:


-- select dbo.FuncGetMIN(123,5,null,null,null)

Alter FUNCTION [dbo].FuncGetMIN(@str float, @str1 float, @str2 float, @str3 float,@str4 float)
RETURNS float
BEGIN

Declare @result int

-- First compare
if @str < @str1
set @result=@str
else
set @result=@str1

-- 2nd compare
if @str2 is null OR @result < @str2
set @result=@result
else
set @result=@str2


-- 3rd compare

if @str3 is null OR @result < @str3
set @result=@result
else
set @result=@str3

-- 4th compare
if @str4 is null OR @result < @str4
set @result=@result
else
set @result=@str4


return @result

end

Advertisements