CODEWITHSUNDEEP
sqloracle-database
toddlermenot
toddlermenot

Reputation: 1618

Sql Max() function on an rownum condition

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.

Upvotes: 0

Views: 5381

Answers (2)

Shannon Severance
Shannon Severance

Reputation: 18410

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>

Upvotes: 3

Andrey
Andrey

Reputation: 60065

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

Upvotes: 0

Related Questions