Select Minimum value from multiple columns

oferprtz · #1 (**permalink**) 09-02-11, 09:25

Hi Guys,

I trying to do a select with a minimum between 5 columns to a single result.

ive tried to do a select with CASE but its not working well if one of the cell's has a null inside.

This is what ive tried:

Code:

Select *,
       Case When dbo.col1 < dbo.col2 and dbo.col1 < dbo.col3 and dbo.col1 < dbo.col4 and dbo.col1 < dbo.col5 then dbo.col1
              When dbo.col2 < dbo.col1 and dbo.col2 < dbo.col3 and dbo.col2 < dbo.col4 and dbo.col2 < dbo.col5 then dbo.col2
              When dbo.col3 < dbo.col1 and dbo.col3 < dbo.col2 and dbo.col3 < dbo.col4 and dbo.col3 < dbo.col5 then dbo.col3
              When dbo.col4 < dbo.col1 and dbo.col4 < dbo.col2 and dbo.col4 < dbo.col3 and dbo.col4 < dbo.col5 then dbo.col4
              When dbo.col5 < dbo.col1 and dbo.col5 < dbo.col2 and dbo.col5 < dbo.col3 and dbo.col5 < dbo.col4 then dbo.col5
       End as TheMin
From dbo.tbl1

Appreciate any ideas.

Thanks!

r937 · #2 (**permalink**) 09-03-11, 03:51

SELECT LEAST(col1,col2,col3,col4,col5)

oferprtz · #3 (**permalink**) 09-03-11, 10:57

Wow! thanks r937!
do you know how i can show all the columns and write the column name as the minimum?

thanks!

r937 · #4 (**permalink**) 09-04-11, 17:25

show all columns? that would be the dreaded, evil "select star"

i'm not sure, though, what your second question means

you can assign a column alias to the minimum...

Code:

SELECT LEAST(col1,col2,col3,col4,col5) AS da_min

however, if you are asking to figure out which of the columns actually contained the minimum, then i must ask you why you need to know this, and why you couldn't figure out everything in your application logic in the first place

oferprtz · #5 (**permalink**) 09-05-11, 19:37

Thanks for the reply.

i dont use an application i just use the workbench as my quick repository so i can pull out quick data.

i just wanted to know which column is the minimum and not use the "AS" so it will be faster to know which one is the min instead of searching by human eye.

thanks,
ofer.

r937 · #6 (**permalink**) 09-06-11, 01:47

well, i suppose you could do this ...

Code:

SELECT da_min  
     , CASE da_min WHEN col1 THEN 'col1'
                   WHEN col2 THEN 'col2'
                   WHEN col3 THEN 'col3'
                   WHEN col4 THEN 'col4'
                             ELSE 'col5' END AS which_col
  FROM ( SELECT LEAST(col1,col2,col3,col4,col5) AS da_min  
           FROM ... ) AS daTable

Internet Services

Web Development

Digital Marketing

Consumer Tech