How to calculate the maximum of two numbers in Oracle SQL select?

Question

This should be simple and shows my SQL ignorance:

SQL> select max(1,2) from dual;
select max(1,2) from dual
       *
ERROR at line 1:
ORA-00909: invalid number of arguments

I know max is normally used for aggregates. What can I use here?

In the end, I want to use something like

select total/max(1,number_of_items) from xxx;

where number_of_items is an integer and can be 0. I want to see total also in this case.

I'm not sure which version of SQL you're using, but I'd also like to throw this out there. SELECT total/IIF(number_of_items < 1, 1, number_of_items) from dual. I believe that should work. — XstreamINsanity, Aug 25 '10 at 14:42

score 105 · Answer 1 · answered Aug 19 '10 at 12:03

105

It looks like you're using Oracle so you can use the greatest function for this in place of max

select total/greatest(1,number_of_items) 
from xxx;

answered Aug 19 '10 at 12:03

Martin Smith

438,706
87
741
845

1

Ah, thanks thats even shorter. I'm selecting on an Oracle dynamic performance view. – Peter G. Aug 19 '10 at 19:35
1

This function is not universal - for example, SQL Server doesn't have such function. +1 anyway – sharptooth Dec 22 '11 at 11:56

score 51 · Answer 2 · edited Nov 23 '17 at 00:10

51

As of Oracle 10.2 they introduced a GREATEST function which does what you want. There is also a LEAST function too.

Examples:

select greatest(1,2) from dual;

GREATEST(1,2)
-------------
            2

select greatest(8,6,4,2) from dual;

GREATEST(8,6,4,2)
-----------------
               8

select greatest(-1,-2) from dual;

GREATEST(-1,-2)
---------------
             -1

select greatest('A','B','CCC','D') from dual;

GREATEST('A','B','CCC','D')
---------------
              D

edited Nov 23 '17 at 00:10

3per

351
9
26

answered Mar 12 '14 at 15:44

Rod Meyer

568
4
7

I love that these were added but it's important to know that when nulls are involved, there are subtle differences between the seemingly equivalent case statements. Consider the results of the following: `SELECT CASE WHEN (1 > null) THEN 1 ELSE null END FROM dual; SELECT CASE WHEN (null > 1) THEN null ELSE 1 END FROM dual; SELECT GREATEST(1, null) FROM dual; SELECT GREATEST(null, 1) FROM dual;` – JimmyJames Jun 30 '23 at 18:20

score 18 · Accepted Answer · answered Aug 19 '10 at 11:16

18

You could use a CASE statement

SELECT Total = CASE WHEN number_of_items > 0 
               THEN total/number_of_items
               ELSE total END
FROM   xxx

answered Aug 19 '10 at 11:16

Lieven Keersmaekers

57,207
13
112
146

score 3 · Answer 4 · edited Apr 09 '19 at 13:22

3

SELECT total/(CASE WHEN number_of_items>1 THEN number_of_items ELSE 1 END) FROM xxx

should work here.......

edited Apr 09 '19 at 13:22

David Balažic

1,319
1
23
50

answered Aug 19 '10 at 11:18

Hari Menon

33,649
14
85
108

score 2 · Answer 5 · edited May 23 '17 at 11:54

You'll have to create a new function for this:

CREATE FUNCTION InlineMax
(
    @p1 sql_variant,
    @p2 sql_variant
)  RETURNS sql_variant
AS
BEGIN
    RETURN 
    CASE 
        WHEN @p1 IS NULL AND @p2 IS NOT NULL THEN @p2 
        WHEN @p2 IS NULL AND @p1 IS NOT NULL THEN @p1
        WHEN @p1 > @p2 THEN @p1
        ELSE @p2 END
END;

Checkout this thread for more details: Is there a Max function in SQL Server that takes two values like Math.Max in .NET?

score 1 · Answer 6 · answered Aug 19 '10 at 11:15

Normally it would be:

SELECT MAX(columnName)
FROM   Table1

Or

SELECT MAX(columnName)
FROM   (SELECT * FROM TableX) AS T1

Or (and this would probably be what you want in your case)

SELECT MAX(value)
FROM   (SELECT 1 AS VALUE FROM DUAL UNION SELECT 2 AS VALUE FROM DUAL)

There may be a cleaner way to do it though.

UPDATE: Using your example of number_of_items and total from table XXX, it'd be:

SELECT TOTAL/MAX(NUMBER_OF_ITEMS)
FROM   XXX

UPDATE 2: Keep in mind, if you allow number of items to be 0, you will get an exception of division by 0. That's why in the other answer the user put a case and the else was the TOTAL, this way you don't get that exception.

score 1 · Answer 7 · answered Dec 14 '17 at 16:17

It is possible to do this in Oracle 8.0 and older (i.e. before CASE was introduced) with the following mathematical trick:

SELECT DECODE(NUMBER_OF_ITEMS-1+ABS(NUMBER_OF_ITEMS-1), 0, 1, NUMBER_OF_ITEMS) AS TOTAL
FROM   xxx

... which is equivalent to max(1,number_of_items).

Replace the three 1s above with another value as required.

This works because number_of_items - 1 goes zero or negative when number_of_items is less than 1. And in general, x + abs(x) is always zero when x <= 0, so the first decode option is matched.

We need this because some of our (3rd party) customers may still be using Oracle 8.0 and it would be many days of effort to find out if or when the last customer will finally upgrade!

score 0 · Answer 8 · answered Jun 25 '22 at 02:21

First, create the function

 CREATE OR REPLACE FUNCTION max_finder(n1 in number, n2 in number)
return number
AS
    n3_max number;
BEGIN
    IF n1<=n2 THEN
        n3_max:=n2;
    ELSE 
        n3_max:=n1;
    END IF;
    return n3_max;
END;
/

Then execute it

DECLARE
    n3_max number;
BEGIN
    n3_max:=max_finder(5,13);
    dbms_output.put_line(n3_max);
END;
/

How to calculate the maximum of two numbers in Oracle SQL select?

8 Answers8