If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > Select Minimum value from multiple columns

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Sep 2011
Posts: 3
Select Minimum value from multiple columns

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!
Reply With Quote
  #2 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
SELECT LEAST(col1,col2,col3,col4,col5)

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Sep 2011
Posts: 3
Wow! thanks r937!
do you know how i can show all the columns and write the column name as the minimum?

thanks!
Reply With Quote
  #4 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Sep 2011
Posts: 3
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.
Reply With Quote
  #6 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On