Re: sql plus max function query

Hmmmm... never thought of that but is it efficient? Will it peel all the records sort and take the highest value not realizing that there can only ever by one set. Technically aggregate functions on the entire table (i.e. no group by clause) don't need a sort step (I think).

Thomas Kyte wrote:

> A copy of this was sent to "Roy Brokvam" <roy.brokvam_at_conax.com>
> (if that email address didn't require changing)
> On Mon, 1 Feb 1999 11:03:00 +0100, you wrote:
>
> >If I get your problem correctly, you will experience this with all the group
> >functions MAX, MIN, AVG, SUM...
> >
> >The reason is that the group functions *always* return one row, no matter
> >how many of your database records match the where-clause. If no database
> >rows match the where-clause, the function simply returns NULL (The MAX of
> >nothing is NULL).
>
> no, group functions do not always return one row (i've seem more then one
> application fail because of this belief -- that the query would always return a
> record).....
>
> Consider:
>
> SQL> l
> 1 select max( created ) from all_users
> 2 where username like '%ZZZZ%'
> 3* group by username
> SQL> /
>
> no rows selected
>
> SQL> select max( created ) from all_users
> 2 where username like '%ZZZZ%';
>
> MAX(CREAT
> ---------
>
> SQL>
>
> if an aggregate has a GROUP BY and no rows meet the where criteria -- no rows
> returned. It is only true that if an aggregate is used and NO group by is used
> that one row will be returned.
>
> two potential answers to the question could be:
>
> SQL> select max(created) from all_users where 1=0 group by 1;
>
> no rows selected
>
> SQL> select * from ( select max(created) created from all_users where 1=0)
> 2 where created is not null
> 3 /
>
> no rows selected
>
> the first one makes use of the fact that GROUP BY on an empty set is the empty
> set. The second one is more clear as to what it is doing...
>
> >
> >Roy
> >
> >sdgreenwood_at_ybs.co.uk wrote in message <793sg9$h6r$1_at_nnrp1.dejanews.com>...
> >>Max function query
> >>-----------------------------
> >>
> >>select max(start_date)
> >>from accounts
> >>where ........etc....
> >>
> >>If there are no rows to satisfy the above query sql plus does not say 'No
> >>Rows Selected'.
> >>
> >>If I ran the above query without the max function 'No Rows Selected' is
> >>displayed.
> >>
> >>The problem is that if the above query is set up in a cursor then %notfound
> >>would not work, therefore processing would continue without testing whether
> >>any rows have been found.
> >>
> >>I know that putting NVL(max(start_date,sysdate)) would work, but can
> >anybody
> >>tell me why the max function works differently.
> >>
> >>Thanks in anticipation,
> >>
> >>SDG
> >>
> >>-----------== Posted via Deja News, The Discussion Network ==----------
> >>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
> >
>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
> Anti-Anti Spam Msg: if you want an answer emailed to you,
> you have to make it easy to get email to you. Any bounced
> email will be treated the same way i treat SPAM-- I delete it.
Received on Mon Feb 01 1999 - 08:11:06 CST