1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

difference between group by and subquery

Discussion in 'SQL PL/SQL' started by lantzej, Jan 14, 2014.

  1. lantzej

    lantzej Guest

    I'm reading oracle 9i sql and there is an example of a subquery which I'm trying to write as a group by query.

    select empno, ename, sal
    from emp
    where sal =
    (select min(sal) from emp);

    This works but I don't understand why I can't write this as a group by expression

    select empno, ename, min(sal)
    from emp
    group by empno, ename
    having min(sal) = sal;

    I'd like to understand what is going on inside of oracle that makes the subquery work but the group by fail.

    Thanks.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    First off you get an error with your query which I know you should be seeing:


    Code (SQL):

    SQL> SELECT empno, ename, MIN(sal)
      2  FROM emp
      3  GROUP BY empno, ename
      4  HAVING MIN(sal) = sal;
    HAVING MIN(sal) = sal
                      *
    ERROR at line 4:
    ORA-00979: NOT a GROUP BY expression
     

    You COULD use another subquery to make your group by query work:


    Code (SQL):

    SQL> SELECT empno, ename, MIN(sal)
      2  FROM emp
      3  GROUP BY empno, ename
      4  HAVING MIN(sal) = (SELECT MIN(sal) FROM emp);


         EMPNO ENAME        MIN(SAL)
    ---------- ---------- ----------
          7369 SMITH             800


    SQL>
     

    but that wouldn't be any better than the first query. You can also do this:


    Code (SQL):

    SQL> SELECT empno, ename, min_sal
      2  FROM
      3  (SELECT empno, ename, MIN(sal) min_sal
      4  FROM emp
      5  GROUP BY empno, ename
      6  ORDER BY 3)
      7  WHERE rownum = 1;


         EMPNO ENAME         MIN_SAL
    ---------- ---------- ----------
          7369 SMITH             800


    SQL>
     

    Let's look at what your group by query is doing that's different from the original. The original query is selecting the empno, ename and sal from emp where the sal is equal to the overall minimum salary in the table:


    Code (SQL):

    SQL> SELECT MIN(sal) FROM emp;


      MIN(SAL)
    ----------
           800


    SQL>
    SQL> SELECT empno, ename, sal
      2  FROM emp WHERE sal = (SELECT MIN(sal) FROM emp);


         EMPNO ENAME             SAL
    ---------- ---------- ----------
          7369 SMITH             800


    SQL>
     

    The original query has a single reference value, the overall minimum sal from emp. Your group by is returning every sal value as a minimum:


    Code (SQL):

    SQL> SELECT empno, ename, MIN(sal)
      2  FROM emp
      3  GROUP BY empno, ename;


         EMPNO ENAME        MIN(SAL)
    ---------- ---------- ----------
          7521 WARD             1250
          7959 QUEEN            5000
          7869 JACK             5000
          7566 JONES            2975
          7844 TURNER           1500
          7876 ADAMS            1100
          7499 ALLEN            1600
          7369 SMITH             800
          7782 CLARK            2450
          7839 KING             5000
          7698 BLAKE            2850


         EMPNO ENAME        MIN(SAL)
    ---------- ---------- ----------
          7900 JAMES             950
          7902 FORD             3000
          7788 SCOTT            3000
          7939 DUKE             5000
          7949 PRINCE           5000
          7654 MARTIN           1250
          7934 MILLER           1300


    18 ROWS selected.



    SQL>
     

    It isn't the same query at all, as there is no overall minimum salary it returns. To 'fix' that you need to order the results by the min(sal) column and then return the very first result as I have already shown.


    I admire your desire to find an equivalent group by query but you can't reference a non-grouped column in the HAVING clause.


    I hope this helps.