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

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 ma开发者_C百科x 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.

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;

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

You could use a CASE statement

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

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

should work here.......

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?

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.

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!

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;
/