Using SQL 2005, is there a way to select the minimum value between 5 columns within one single row of data?
So, if I have a row of data like this:
id num1 num2 num3 num4 num5
1 22 51 4 99 34
Then, how can I get the lowest value using SQL?
You can create a UDF.
create function GetMin(@N1 int, @N2 int, @N3 int, @N4 int, @N5 int)
returns table as
return (select min(N) as Value
from (select @N1
union all
select @N2
union all
select @N3
union all
select @N4
union all
select @N5) as T(N))
And use it like this.
declare @T table
(
id int,
num1 int,
num2 int,
num3 int,
num4 int,
num5 int
)
insert into @T values
(1, 22, 51, 4, 99, 34),
(2, 222, 251, 24, 299, 234)
select id,
M.Value
from @T
cross apply dbo.GetMin(num1, num2, num3, num4, num5) as M
Or you can skip the UDF and use the query directly.
select id,
M.Value
from @T
cross apply (select min(N) as Value
from (select num1
union all
select num2
union all
select num3
union all
select num4
union all
select num5) as T(N)) as M
probably something like
select id
, least (num1, num2, num3, num4, num5)
from your_table
/
Most flavours of RDBMS offer LEAST().
Fix your data structure to be normalized so that you don't have to do this complex, performance killing stuff to get the information you need.
You can calculate min using the formula : MIN(a,b) = (a+b)/2 - abs(a-b)/2
TSQL can do it, but it takes a little prep...
First, you need a function:
(it takes a comma-delimeted string of integers, and returns the greatest integer)
CREATE Function [dbo].[GreatestInt]
( @Array varchar(max) )
Returns int As
BEGIN
DECLARE @end Int
DECLARE @start Int
DECLARE @tbl_int Table (myInt Int)
DECLARE @return Int
SET @Array = @Array + ','
SET @start=1
SET @end=1
WHILE @end<Len(@Array)
BEGIN
SET @end = CharIndex(',', @Array, @end)
INSERT INTO @tbl_int
SELECT
Cast(Substring(@Array, @start, @end-@start) As Int)
SET @start=@end+1
SET @end = @end+1
END
SET @return = (SELECT MAX(myInt) FROM @tbl_int)
RETURN @return
END
And then to create your string of integers (this is the part TSQL isn't very good at):
(in the SELECT)
stuff(
stuff([num5], 1, 0,',')
,1,0,
stuff(
stuff([num4], 1, 0,',')
,1,0,
stuff(
stuff([num3], 1, 0,',')
,1,0,
stuff(
stuff([num2], 1, 0,',')
,1,0,
[num1]
)
)
)
)
So to use the function:
SELECT
id,
dbo.GreatestInt( stuff(
stuff([num5], 1, 0,',')
,1,0,
stuff(
stuff([num4], 1, 0,',')
,1,0,
stuff(
stuff([num3], 1, 0,',')
,1,0,
stuff(
stuff([num2], 1, 0,',')
,1,0,
[num1]
)
)
)
)
)
FROM
myTable
The reason I did it like this, instead of the way @mikael did in his answer (wich I +1'd, because it does answer your question), is that this approach will work on any number of fields, not just 5. But honestly, TSQL does have some room to improve here -- they really need a tsql version of plsql's greatest/least functions. Oh well...
SELECT id
, CASE WHEN num1 < num2 AND num1 < num3 AND num1 < num4 AND num1 < num5
THEN num1
WHEN num2 < num3 AND num2 < num4 AND num2 < num5
THEN num2
WHEN num3 < num4 AND num3 < num5
THEN num3
WHEN num4 < num5
THEN num4
ELSE num5
END AS LeastNum
FROM MyTable
