SQL Selecting MIN value from row data, not column data

Question

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?

FYI if your data represents the same information, they should be in **ROWS** not **FIELDS**. — JNK, Aug 17 '11 at 18:22
Try this: http://stackoverflow.com/questions/368351/whats-the-best-way-to-select-the-minimum-value-from-multiple-columns — reggie, Aug 17 '11 at 19:00
Please specify / tag what version of SQL Server you are using. Solutions can vary because newer versions of SQL Server have more elaborate syntax available (such as window functions). Not that those would be used here, just trying to encourage the practice of always specifying. — Aaron Bertrand, Aug 17 '11 at 19:41
possible duplicate of [SQL Server equivalent to Oracle LEAST?](http://stackoverflow.com/questions/1972051/sql-server-equivalent-to-oracle-least) — APC, Aug 18 '11 at 08:30

score 2 · Answer 1 · answered Aug 17 '11 at 18:21

2

probably something like

select id
       , least (num1, num2, num3, num4, num5)
from your_table
/

Most flavours of RDBMS offer LEAST().

answered Aug 17 '11 at 18:21

APC

144,005
19
170
281

MSSQL can't do this, which is what I am using. – djangofan Aug 17 '11 at 18:40

score 2 · Answer 2 · answered Aug 17 '11 at 20:46

2

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.

answered Aug 17 '11 at 20:46

HLGEM

94,695
15
113
186

This should be voted more than once if possible. It's the best advice indeed. – ypercubeᵀᴹ Aug 17 '11 at 20:49

Mikael Eriksson · Accepted Answer · 2011-08-17T18:52:13.943

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

@djangofan - It will work with varchar() but all fields have to be the same data type. Either by them self or you need to cast them. — Mikael Eriksson, Aug 18 '11 at 04:07

score 0 · Answer 4 · answered Aug 17 '11 at 18:22

0

You can calculate min using the formula : MIN(a,b) = (a+b)/2 - abs(a-b)/2

answered Aug 17 '11 at 18:22

a1ex07

36,826
12
90
103

score 0 · Answer 5 · answered Aug 17 '11 at 19:20

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...

score 0 · Answer 6 · answered Aug 17 '11 at 20:43

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

SQL Selecting MIN value from row data, not column data

6 Answers6

Linked