SQL Server Performance

Select minimum of two columns ?

Discussion in 'ALL SQL SERVER QUESTIONS' started by Chappy, Jan 7, 2003.

  1. Chappy New Member

    This seems like a trivial question, but I just cant think how to do it (maybe my brain is frazzled after xmas!)


    col1 col2
    7 9
    5 3

    how can i select the lowest value from these two columns, returning something like


    Im using SQL 7.0 in this instance, and so I cant just write my own UDF. Thanks
  2. tkelley New Member

    Hmm.. have you tried the min function...
    SELECT MIN(col1), MIN(col2) FROM table INNER JOIN...
    Are there keys on the table? Hope this helps..

    T Kelley

  3. Chappy New Member

    Hi tkelley.
    Not entirely sure what you mean by inner join. How will this help me?

    MIN is an aggregate function and takes the minimum value which exists in one of the rows, for a given column. What Im hoping for is instead something like...

    select SmallerOfTwoValues(Column1, Column2) from Table
    rather like you might expect a MIN function to work in procedural programming languages such as C.

    The table in question does have keys on it, and already has a lot of complex joining and subselects being used to actually calculate these two columns.

  4. Chappy New Member

    Turns out I was able to answer my own question <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><pre><br />select Col1, Col2, <br /> CASE (Col1 - Col2)<br /> WHEN 0 THEN Col1 <br /> ELSE<br /> CASE CONVERT(INTEGER, (Col1 - Col2) / ABS(Col1 - Col2))<br /> WHEN -1 THEN Col1<br /> WHEN 1 THEN Col2<br /> END <br /> END AS Lowest<br />FROM TestData<br /></pre><br />The nested CASE statement was necessary to prevent a divide by zero in the inner CASE (when both Col1 and Col2 are equal). If you want to play around with this, heres the script which generates some test data. <br /><pre><br />CREATE TABLE [dbo].[TestData] (<br />[ID] [int] IDENTITY (1, 1) NOT NULL ,<br />[Col1] [int] NULL ,<br />[Col2] [int] NULL <br />) ON [PRIMARY]<br />GO<br /><br />INSERT into TestData Values (5, 6)<br />INSERT into TestData Values (0, 5)<br />INSERT into TestData Values (5, 0)<br />INSERT into TestData Values (100, 100)<br />INSERT into TestData Values (-2, 22)<br />INSERT into TestData Values (0, 0)<br /></pre><br />Who knows, it might be useful to someone else using SQL 7, and not able to use UDF's, but please still let me know if anyone found a simpler solution.<br />Thanks.<br /><br />
  5. bradmcgehee New Member

    Thanks for answering your own question, and your contributions.

    Brad M. McGehee
  6. Graeme New Member

    If anyone is reading this, a better answer to the above would be:

    select Col1, Col2,
    WHEN (Col1 > Col2) THEN Col2
    ELSE Col1
    END AS Lowest
    FROM TestData

    Or, if used more often, create a scalar function:

    CREATE FUNCTION dbo.F_Smallest(@valueA as INTEGER, @valueB as INTEGER)

    IF (@valueB > @valueA)
    SET @Result = @valueA
    SET @Result = @valueB

    RETURN @Result

    Obviously, replace INTEGER for whatever datatype you're using.

    There's no need for ABS() or divides.

    Hope this helps.
  7. Madhivanan Moderator

    A reply to 9 year old thread :). I dont think a UDF is needed here. It is as simple as using a CASE expression
  8. FrankKalis Moderator

    When I saw this, I remembered we had a long and intensive discussion years ago about that topic here with various approaches and performance measures and all that. For some reason however, I can't find it though. :(
  9. Graeme New Member

    That's the way I roll :cool:

    I agree there's no need. If it's a once-off, I would just use the CASE expression.
    However, if you need to do this in many places, or if the expressions are more complicated, it might be better using a function for readability and maintainability.

    For instance, I would prefer to maintain this:

    select dbo.F_Smallest(
    SQRT(ValA1 * ValA2) / CASE Level WHEN 1 THEN 5 WHEN 2 THEN 10 ELSE 3 END, X*X*X * 3 + X*Y + 21 +C) as Lowest
    FROM TestData

    Over this:

    select CASE
    WHEN ( (SQRT(ValA1 * ValA2) / CASE Level WHEN 1 THEN 5 WHEN 2 THEN 10 ELSE 3 END) > (X*X*X * 3 + X*Y + 21 +C) ) THEN X*X*X * 3 + X*Y + 21 +C
    ELSE SQRT(ValA1 * ValA2) / CASE Level WHEN 1 THEN 5 WHEN 2 THEN 10 ELSE 3 END
    END AS Lowest
    FROM TestData

Share This Page