2

I've been looking for a good equivalent to the Oracle LEAST function.

I'm hoping to implement a user defined function that does about 10 fairly complex calculations, and takes the minimum value from each of those calculations.

What I would do in Oracle is: 

SELECT LEAST
(
select expression1 from dual,
select expression2 from dual,
select expression3 from dual
) from dual

See http://www.techonthenet.com/oracle/functions/least.php for more on Oracle LEAST.

If expression1 returned 10, expression2 return 5, and expression3 reeturned 30, the whole expression would return 5.

Because this may be about 10-20 calculations, the CASE WHEN syntax is going to get unwieldy fast.

Although the code will be more readable if we break it up more, I thought it would be more efficient to do it within one database query. Let me know I'm incorrect on that point, please!

I.e., is a stored procedure with 20 simple queries significantly slower than a stored procedure with one query that references a lot of tables all in one query.

Jade
  • 1,790
  • 2
  • 18
  • 22
  • 2
    May be worth looking at http://dba.stackexchange.com/questions/21542/what-is-the-most-efficient-way-to-get-the-minimum-of-multiple-columns-on-sql-ser - it handles GREATEST vs. LEAST but the concepts should be equivalent. – Aaron Bertrand Aug 13 '12 at 13:59
  • Take a look at several solutions here: http://stackoverflow.com/questions/124417/is-there-a-max-function-in-sql-server-that-takes-two-values-like-math-max-in-ne – Vadzim Jan 22 '16 at 12:46
  • Native support: [GREATEST/LEAST](https://stackoverflow.com/a/67096351/5070879) – Lukasz Szozda Apr 14 '21 at 17:25

3 Answers3

5

mayby this query could help:

 SELECT  min(c1)  
 from ( 
      select expression1 as c1  
      union all
      select expression2 as c1 
      union all 
      select expression3 as c1
 )
afftee
  • 2,221
  • 1
  • 16
  • 14
  • A derived table (from a table value constructor) can accomplish something very similar. The semantics are almost the same as the example above - the main difference in the syntax, which may be more or less compact depending on context. There's a good example [here](https://msdn.microsoft.com/en-us/library/dd776382.aspx#Anchor_5), under community additions. – unbob Apr 29 '16 at 18:29
0

The function Least() is applied horizontally in Oracle (on a row level), while Min() is applied vertically over a column. The example in the question required Min().

Stanislav Voynovski
  • 11
  • 1
  • Thanks for the reply, but a correct, detailed and accepted answer has been given 7 years ago... – AnoE Apr 08 '16 at 11:27
0

Azure SQL DB and future SQL Server versions now have greatest/least implemented:

https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-greatest-transact-sql?view=sql-server-ver15

Conor Cunningham MSFT
  • 4,151
  • 1
  • 15
  • 21