SELECT
n5  =case   when n1>=n2 and n1>=n3 and n1>=n4 then n1
            when n2>=n1 and n2>=n3 and n2>=n4 then n2
     ...
FROM
     ...
WHERE
     ...

declare @t table (rowid int identity(1,1), n1 int, n2 int, n3 int, n4 int, [min] int)
insert @t (n1,n2,n3,n4,[min])
select 1,1,1,2,1 union all
select 1,2,3,4,2 union all
select 2,3,4,5,2 union all
select 3,4,5,6,4

--2005 or later
--This solution assumes you have some row identifier
select * 
from @t
where  rowid in 
       (
       select rowid
       from   @t
       unpivot (n for col in ([n1],[n2],[n3],[n4])
       ) up
       group by rowid
       having min(n) != min([min])
       )


--any version
select n1,n2,n3,n4,[min]
from @t
where  [min] > n1
or     [min] > n2
or     [min] > n3
or     [min] > n4

OUTPUT:
rowid       n1          n2          n3          n4          min
----------- ----------- ----------- ----------- ----------- -----------
2           1           2           3           4           2
4           3           4           5           6           4

n1          n2          n3          n4          min
----------- ----------- ----------- ----------- -----------
1           2           3           4           2
3           4           5           6           4

quote:

---

Originally posted by sakets_2000

you'll need to write case statements.

SELECT
n5  =case   when n1>=n2 and n1>=n3 and n1>=n4 then n1
            when n2>=n1 and n2>=n3 and n2>=n4 then n2
     ...
FROM
     ...
WHERE
     ...

---

select
	[Max_of_n1_to_n4] =     
  	(
  	select
   		X1= max(bb.xx)
  	from
   		(
   		select xx = a.n1 where a.n1 is not null union all
   		select xx = a.n2 where a.n2 is not null union all
   		select xx = a.n3 where a.n3 is not null union all
   		select xx = a.n4 where a.n4 is not null
   		) bb
  	)
from
 	MyTable a

select
   	[Max_of_n1_to_n4] =  
 	case
 	when  a.n1 is not null   and
  		(a.n1 >= a.n2 or a.n2 is null) and
  		(a.n1 >= a.n3 or a.n3 is null) and
  		(a.n1 >= a.n4 or a.n4 is null)
 	then 	a.n1
 	when  	a.n2 is not null   and
  		(a.n2 >= a.n1 or a.n1 is null) and
  		(a.n2 >= a.n3 or a.n3 is null) and
  		(a.n2 >= a.n4 or a.n4 is null)
 	then 	a.n2
 	when  	a.n3 is not null   and
  		(a.n3 >= a.n1 or a.n1 is null) and
  		(a.n3 >= a.n2 or a.n2 is null) and
  		(a.n3 >= a.n4 or a.n4 is null)
 	then 	a.n3
 	when  	a.n4 is not null   and
  		(a.n4 >= a.n1 or a.n1 is null) and
  		(a.n4 >= a.n2 or a.n2 is null) and
  		(a.n4 >= a.n3 or a.n3 is null)
 	then a.n4
 	else null
 	end
from
 	MyTable a

quote:

---

Using a subquery:

select
	[Max_of_n1_to_n4] =     
  	(
  	select
   		X1= max(bb.xx)
  	from
   		(
   		select xx = a.n1 where a.n1 is not null union all
   		select xx = a.n2 where a.n2 is not null union all
   		select xx = a.n3 where a.n3 is not null union all
   		select xx = a.n4 where a.n4 is not null
   		) bb
  	)
from
 	MyTable a

---

quote:

---

Originally posted by sakets_2000

quote:

---

Using a subquery:

select
	[Max_of_n1_to_n4] =     
  	(
  	select
   		X1= max(bb.xx)
  	from
   		(
   		select xx = a.n1 where a.n1 is not null union all
   		select xx = a.n2 where a.n2 is not null union all
   		select xx = a.n3 where a.n3 is not null union all
   		select xx = a.n4 where a.n4 is not null
   		) bb
  	)
from
 	MyTable a

---

Not sure on this. Wouldn't this return the same value always against all rows ?

---