Sql Max() function on an rownum condition

Question

I saw something like the following in our production code:

select max(col_val) from table_name where 
--set of conditions here
--AND
rownum=1;

This looked strange to me. Would the above code execute with the intended purpose?(selecting a max value from a set of values). Won't this select always return a single record from which the col_val would be chosen?. Thanks.

Answer 1

it will take the first row that satisfies conditions. max seems to be excess here.

Answer 2

No. It is guarenteed to get the max of a set of values. It will return the first value only, where first value is driven by execution plan. Depending on the plan, the first value may be the max value also, but this could change because plans are not constant.

SQL> create table t (i number);

Table created.

SQL> insert into t values (1);

1 row created.

SQL> insert into t values (2);

1 row created.

SQL> select i from t;

     I
----------
     1
     2

SQL> select max(i) from t;

    MAX(I)
----------
     2

SQL> select max(i) from t where rownum = 1;

    MAX(I)
----------
     1

SQL>