Function to find the lower of two (or more) values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Function to find the lower of two (or more) values

Author

Topic

Eddie M
Starting Member

14 Posts

Posted - 2011-09-25 : 12:08:46

Greeting. This is my first post in this forum, so Hello to you all.

I have a table that contains two columns, both of the same data type.

For each row, I want to extract whichever of the two columns contains the lower value. So if one row contains 5 and 7, the result for that row will be 5. If another row contains 10 and 8, the result will be 8. And so on.

This is what I have so far:


SELECT 
 CASE WHEN ColA < ColB THEN ColA ELSE ColB END
 FROM TheTable

This works OK, but I was wondering if there was a simpler solution?

Is there a function in T-SQL that simply returns the lower of two values? Something like LOWEROF(Val1, Val2)? It seems a bit clumsy to have to use a CASE construct for such a simple requirement.

Thanks in advance.

Eddie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-25 : 12:56:34

Unfortunately no. There's no function which can be applied to more than one column to get lowest of them. For that you've to write a UDF if you have to dispense with case...when

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-25 : 13:11:16

another way is to get them into rows and then get min of them using UNION ALL or UNPIVOT but I would certainly prefer CASE..WHEN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-25 : 18:08:25

SELECT *, MinValue
FROM dbo.Table
CROSS APPLY (SELECT MIN(t) FROM (VALUES(Col1), (Col2), (Col3), (Col4)) AS d(t)) AS f(MinValue)

N 56°04'39.26"
E 12°55'05.63"

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-25 : 22:25:07

I would ideally like to have the data in separate rows rather than in columns if we have to find the MIN or MAX of it :) As already pointed out I would also have gone with CASE..WHEN statement only to get this working.

Alternatively let me show you on how to make use of UNION ALL to get this working.

Create table tblValues
(
Sno INT,
Col1 INT,
Col2 INT,
Col3 INT
)
GO

INSERT INTO tblValues
SELECT 1, 33, 4, 6 UNION ALL
SELECT 2, 10, 6, 40 UNION ALL
SELECT 3, 8, 1, 2
GO

SELECT
Sno,
(
SELECT MIN(V.A)
FROM
(
SELECT M.Col1 AS A UNION ALL
SELECT M.Col2 AS A UNION ALL
SELECT M.Col3 AS A
) AS V
) AS [Minimum Value]
FROM
tblValues AS M
GO

DROP TABLE tblValues
GO

Best Regards
Vadivel

http://vadivel.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 00:25:52

quote:
Originally posted by SwePeso

SELECT *, MinValue
FROM dbo.Table
CROSS APPLY (SELECT MIN(t) FROM (VALUES(Col1), (Col2), (Col3), (Col4)) AS d(t)) AS f(MinValue)

N 56°04'39.26"
E 12°55'05.63"

Will it work below 2008? I dont have 2005 box to test here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-26 : 03:09:15

No, the VALUES constructor is 2008 and above.

N 56°04'39.26"
E 12°55'05.63"

Eddie M
Starting Member

14 Posts

Posted - 2011-09-26 : 03:32:39

Thank you all for these very helpful replies.

For now, I'm going to stay with the CASE construct. I really just wanted to be sure I wasn't missing something obvious.

But your other suggestions are very interesting and worth knowing about. I can see I'm going to learn a lot from this forum.

Just to give some background: In this particular case, there are only ever two values to choose from. One is a cost price and the other is a selling price. I am required to value an item of inventory based on either its cost or selling price, whichever is lower. The row doesn't contain a long list of values; if it did, the table would probably have been normalized in some way.

So thanks again to you all.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-26 : 04:01:57

quote:
Originally posted by SwePeso

No, the VALUES constructor is 2008 and above.

N 56°04'39.26"
E 12°55'05.63"

I thought so. thats why i didnt suggest it as this was posted in new to sql server forum and wasnt sure if OP was on 2008

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Eddie M
Starting Member

14 Posts

Posted - 2011-09-26 : 10:34:04

In fact, I'm still in 2005. Still, it was interesting to read about the VALUES construct, if only to whet my appetite for 2008.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources