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 col2
5 3 how can i select the lowest value from these two columns, returning something like
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.. ———-
MS, MCDBA, OCA, CIW
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=’‘ /><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 />
Thanks for answering your own question, and your contributions.
Brad M. McGehee
If anyone is reading this, a better answer to the above would be: select Col1, Col2,
WHEN (Col1 > Col2) THEN Col2
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)
DECLARE @Result INTEGER IF (@valueB > @valueA)
SET @Result = @valueA
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 . I dont think a UDF is needed here. It is as simple as using a CASE expression
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.
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
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
A reply to 9 year old thread .
I dont think a UDF is needed here. It is as simple as using a CASE expression