SQL Server Performance Forum – Threads Archive
Select minimum of two columns ?
This seems like a trivial question, but I just cant think how to do it (maybe my brain is frazzled after xmas!) table.. col1 col27 9
5 3 how can i select the lowest value from these two columns, returning something like
COLUMN0
——-
7
3 Im using SQL 7.0 in this instance, and so I cant just write my own UDF. Thanks
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
MS, MCDBA, OCA, CIW
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. Regards
Turns out I was able to answer my own question <img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
Thanks for answering your own question, and your contributions.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
If anyone is reading this, a better answer to the above would be: select Col1, Col2,
CASE
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)
RETURNS INTEGER
AS
BEGIN
DECLARE @Result INTEGER IF (@valueB > @valueA)
SET @Result = @valueA
ELSE
SET @Result = @valueB RETURN @Result
END Obviously, replace INTEGER for whatever datatype you’re using. There’s no need for ABS() or divides. Hope this helps.
A reply to 9 year old thread
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
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.
![Frown :( :(](styles/default/xenforo/smilies/frown.png)
![Cool :cool: :cool:](styles/default/xenforo/smilies/cool.png)
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
A reply to 9 year old thread
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
I dont think a UDF is needed here. It is as simple as using a CASE expression
]]>