Select lesser value of multiple columns in the same row

[BillLumbergh]

Assume I have a table with columns A, B, and C, all of which are of a numeric datatype. I want to write a select that will bring back the lowest value of the three. The idea is similar to using the MIN() function, but instead of choosing the minimum value out of a series of rows, want the min of a series of columns. So, If I have 10 rows, I would get a recordset with one column and 10 records. Is this possible? I imagine it could be done with a union of some sort, but I'm looking for a SQL function similar to MIN() that will do it for me.

Thanks in advance for any help.

 

[JohnDTampaBay]

There's not a function that I can find to do this. You can do it with a CASE statement. If you have more than three columns to compare, its going to look ugly. Check out this example:

create table #temptable (
	ID int identity(1,1)
	, ColA	int
	, ColB	int
	, ColC	int
	)

insert #temptable (ColA, ColB, ColC) values (1,2,3)
insert #temptable (ColA, ColB, ColC) values (9,8,7)

select ID, CASE
		WHEN ColA < ColB AND ColA < ColC THEN ColA
		WHEN ColB < ColA AND ColB < ColC THEN ColB
		WHEN ColC < ColA AND ColC < ColA THEN ColC
	END AS Smallest
from #tempTable

Or it could be done using a UNION as you suggested:

create table #temptable (
	ID int identity(1,1)
	, ColA	int
	, ColB	int
	, ColC	int
	)

insert #temptable (ColA, ColB, ColC) values (1,2,3)
insert #temptable (ColA, ColB, ColC) values (9,8,7)

select id, min(Col) AS Smallest from (
  select ID, ColA AS Col from #temptable
  UNION
  select ID, ColB AS Col from #temptable
  UNION
  select ID, ColC AS Col from #temptable) a
group by id

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.