CODEWITHSUNDEEP
sqlsql-servert-sql
m0fo
m0fo

Reputation: 2189

Getting lower value out of 3 values in SQL SERVER

How can I get the lowest out of 3 values in Sql Server?

The same function in MySQL called LEAST, and comparison in SQL Server?

Upvotes: 4

Views: 16738

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can do this without a nested case:

select (case when val1 < val2 and val1 < val3 then val1
             when val2 < val3 then val2
             else val3
        end) as least_of_three

This seems pretty clear as to what it is doing. It also generalizes pretty easily.

Do be careful about NULLs -- which LEAST and GREATEST ignore. If you need to handle these, then it is a bit more cumbersome:

select (case when val1 <= coalesce(val2, val1) and val1 <= coalesce(val3, val1) then val1
             when val2 <= coalesce(val3, val2) then val2
             else val3
        end) as least_of_three

Notice I've changed the "<" to "<=". I use the coalesce to "ignore" the value by evaluating to true. So, val1 is always less than val2, if val2 is null. I've chosen this method because it works for all data types (strings, numbers, dates).

EDIT:

I don't usually go back and write answers on questions seven years old, but this question is clearly SQL Server. The best approach uses apply:

select t.*, max_val
from t cross apply
     (select max(v.val) as max_val
      from (values (t.val1), (t.val2), . . .
     ) v(val);

Note that this solution does not work in MySQL because it does not support lateral joins.

Upvotes: 9

AmmarR
AmmarR

Reputation: 248

there is no function in SQL server similar to Least() function in mysql

the Least() function of MySQL does the following

Take two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the following rules:

If any argument is NULL, the result is NULL. No comparison is needed.

If the return value is used in an INTEGER context or all arguments are integer-valued, they are compared as integers.

If the return value is used in a REAL context or all arguments are real-valued, they are compared as reals.

If the arguments comprise a mix of numbers and strings, they are compared as numbers.

If any argument is a nonbinary (character) string, the arguments are compared as nonbinary strings.

In all other cases, the arguments are compared as binary strings.

The Min() function of sql server returns the smallest value of a column in a table it doesn't take arguments

here is a simulation but still its not like Least because it can only take in integers

CREATE FUNCTION [dbo].[GetSmallest]
(
    -- Add the parameters for the function here
    @val1 int,  @val2 int,  @val3 int
)
RETURNS int
AS
BEGIN
Declare @result int
set @result = case when @val1 < @val2 then
               case when @val1 < @val3 then 
                @val1
               else
                @val3
               end
        when @val2 < @val3 then 
                @val2
        else
                 @val3
    end
  return @result  
END

here is how you call it

SELECT [dbo].[GetSmallest] (   32  ,31  ,6)

Upvotes: 2

Mike Ryan
Mike Ryan

Reputation: 4374

And the obvious nested case statement would be:

select case when val1 < val2 then
               case when val1 < val3 then val1
               else val3
               end
        when val2 < val3 then val2
               else val3
    end
from cols;

on 

create table cols (val1 int, val2 int, val3 int);

It's not going to scale very easily to higher # of comparisons, and it doesn't include coalesce statements to suppress nulls.

The singular advantage is that another programmer will be able to figure it out quickly when they look at your code. (And unless this is a performance bottleneck, I think that's a pretty good consideration!)

Upvotes: 4

dan radu
dan radu

Reputation: 2782

If you want to avoid nested CASE statements, this will do (although a bit awkward):

CREATE TABLE #t(ID INT, V1 INT, V2 INT, V3 INT)
INSERT INTO #t VALUES
    (1, 10, 20, 30)
    , (2, 60, 50, 40)

SELECT * FROM #t t1
CROSS APPLY (SELECT MAX(V), MIN(V) FROM (
SELECT V1 AS V
UNION SELECT V2
UNION SELECT V3 
) T) AS t2(VMAX, VMIN)

DROP TABLE #t

Upvotes: 2

ron tornambe
ron tornambe

Reputation: 10780

You can use the "Min" function in T-SQL:

SELECT MIN(Value) FROM MyTable

Upvotes: -5

Related Questions