SQLTeam.com | Weblogs | Forums

Get the minimum value in multiple columns

sql2008
sql2012
sql2014
sql2008r2

#1

Hi ,

I have this table -

create table t(id int , x1 int,x2 int,x3 int ,x4 int ,x5 int );

insert into t select 5555,4,3,5,2,1
select 6666,3,4,7,8,1
select 7777,2,5,7,8,99;

and i need to get the minimum value for each id -
5555 - 1
6666 - 3
7777 - 2


#2
SELECT	id,
	(
		SELECT	MIN(X) 
		FROM (VALUES (x1), (x2), (x3), (x4), (x5)) AS value(X)
	) as [MinX]
FROM	t

NULL values will be fine, but you will get a warning (which might upset an APP)