Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

ANY_VALUE set function

Kevan GellingMay 19 2016

In the example below, I want to display the department number, department name and number of employees in each department.  I should only need to group by DEPTNO, but I have to include DNAME in my GROUP BY clause in order to avoid an "ORA-00979: not a GROUP BY expression" erro

SELECT  d.deptno

      , d.dname 

      , COUNT(*)

FROM    dept d

  JOIN  emp e  ON e.deptno = d.deptno

GROUP BY d.deptno

       , d.dname ;

But including DNAME in the GROUP BY can lead to a worse execution plan (as the optimiser may need to look past the DEPTNO index to check DNAME values).  I also think, it can be misleading - it implies that I want employee totals for each combination of department number and name.

A workaround is to use MIN or MAX (as shown), but I think this can be misleading and still requires superfluous work to be done by Oracle to determine the minimum or maximum value.

SELECT  d.deptno

      , MAX( d.dname )  AS dname

      , COUNT(*)

FROM    dept d

  JOIN  emp e  ON e.deptno = d.deptno

GROUP BY d.deptno ;

---------------------

An ANY_VALUE()  function would remove the optimiser redundancy (i.e. a DNAME grouping or min/max sorting) and allow for more readable code.

SELECT  d.deptno

      , ANY_VALUE( d.dname )  AS dname

      , COUNT(*)

FROM    dept d

  JOIN  emp e  ON e.deptno = d.deptno

GROUP BY d.deptno ;

Comments
Post Details
Added on May 19 2016
16 comments
1,504 views