Results 1 to 6 of 6
  1. #1
    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT LEAST(col1,col2,col3,col4,col5)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    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!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •