case
  when Col1 => Col2 then Col1
  when Col2 > Col1 then Col2
  else null
end

GO

/****** Object:  UserDefinedFunction [dbo].[least]    Script Date: 08/23/2012 00:53:38 ******/

/*Author: Rakesh
Description: This works good for numbers and alphabet.
*/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[least] (@str1 nvarchar(max),@str2 nvarchar(max))
RETURNS nvarchar(max)
BEGIN

	DECLARE @retVal nvarchar(max);

	set @retVal = (select case when @str1<=@str2 then @str1 end as retVal)

	RETURN @retVal;
END;
GO

select dbo.least(10,100) LEAST_OF_TWO,  dbo.least('R','S') LEAST_OF_TWO_ALPHABET

GO

/****** Object:  UserDefinedFunction [dbo].[greatest]    Script Date: 08/23/2012 01:00:56 ******/
/*Author: Rakesh
Description: This works good for numbers and alphabet.
*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create function [dbo].[greatest] (@str1 nvarchar(max),@str2 nvarchar(max))
RETURNS nvarchar(max)
BEGIN

	DECLARE @retVal nvarchar(max);

	set @retVal = (select case when @str1<=@str2 then @str2 end as retVal)

	RETURN @retVal;
END;
GO

select dbo.greatest(10,100) GREATEST_OF_TWO, dbo.greatest('a','z') GREATEST_OF_TWO_ALPHABET 

case
  when Col1 => Col2 then Col1
  when Col2 > Col1 then Col2
  else null
end

case
when  a.Val1 is not null   and
	(a.Val1 >= a.Val2 or a.Val2 is null) and
	(a.Val1 >= a.Val3 or a.Val3 is null) and
	(a.Val1 >= a.Val4 or a.Val4 is null)
then 	a.Val1
when  	a.Val2 is not null   and
	(a.Val2 >= a.Val1 or a.Val1 is null) and
	(a.Val2 >= a.Val3 or a.Val3 is null) and
	(a.Val2 >= a.Val4 or a.Val4 is null)
then 	a.Val2
when  	a.Val3 is not null   and
	(a.Val3 >= a.Val1 or a.Val1 is null) and
	(a.Val3 >= a.Val2 or a.Val2 is null) and
	(a.Val3 >= a.Val4 or a.Val4 is null)
then 	a.Val3
when  	a.Val4 is not null   and
	(a.Val4 >= a.Val1 or a.Val1 is null) and
	(a.Val4 >= a.Val2 or a.Val2 is null) and
	(a.Val4 >= a.Val3 or a.Val3 is null)
then a.Val4
else null
end

Select
	[Max_of_Val1_to_Val4] =     
	  	(
	  	select
	   		X1= max(bb.xx)
	  	from
	   		(
	   		select xx = a.Val1 where a.Val1 is not null union all
	   		select xx = a.Val2 where a.Val2 is not null union all
	   		select xx = a.Val3 where a.Val3 is not null union all
	   		select xx = a.Val4 where a.Val4 is not null
	   		) bb
	  	)
from
	MyTable a

DECLARE @T AS TABLE
(
    pk      integer PRIMARY KEY,
    col1    integer NULL,
    col2    integer NULL,
    col3    integer NULL,
    col4    integer NULL
);

INSERT @T
VALUES 
    (1, 4, 3, 2, 1),
    (2, 5, NULL, 7, 8);

SELECT
    MAX(f.x) AS Greatest
FROM @T AS t
CROSS APPLY
    (VALUES (col1), (col2), (col3), (col4)) AS f (x)
GROUP BY t.pk

SELECT MyTable1.x, MyTable2.y,..., G.Greatest
FROM MyTable1
	JOIN MyTable2 ON MyTable1.key = MyTable2.key
	.
	.
	CROSS APPLY (
			SELECT MAX(T.v) AS Greatest
			FROM ( VALUES (MyTable1.value), (MyTable2.value), ... ) AS T(v)
		) AS G

DECLARE @T AS TABLE
(
    pk      integer PRIMARY KEY,
    col1    integer NULL,
    col2    integer NULL,
    col3    integer NULL,
    col4    integer NULL
);

INSERT @T VALUES (1, 4, 3, 2, 1);
INSERT @T VALUES (2, 5, NULL, 7, 8);

SELECT Greatest=MAX(col), Least=MIN(col)
FROM @T
CROSS APPLY (
    SELECT col1 UNION ALL SELECT col2 UNION ALL SELECT col3
    UNION ALL SELECT col4) a(col)
GROUP BY pk