HAVING CLAUSE is haunting me
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: HAVING CLAUSE is haunting me

  1. #1
    Join Date
    Jul 2009
    Posts
    10

    HAVING CLAUSE is haunting me

    1)Friends i have a BIG confusion with HAVING CLAUSE.

    Syntax:
    HAVING

    In this expression part we use some normal columns(ex: HAVING deptno=20),Group function on some column(ex HAVING max(sal)>3000) or both combination of normal column and group function(ex: HAVING max(sal)>3000 and deptno=20).
    Now, the columns which we are using in HAVING CLAUSE (in above examples sal,deptno) do they need to be present in both SELECT CLAUSE and GROUP BY CLAUSE or in either of the clause or they need not present in SELECT and GROUP BY clause.
    I have tried a lot to get the answer for it but nobody has given a clear answer.
    Friends please help me with this having clause GHOST its haunting me.

    2)Query to retrieve the deptno of the employee who gets highest salary paid

    SELECT deptno FROM emp HAVING sal=MAX(sal);
    i am getting error.
    Though there are many ways to get the answer for this query i want to know why this query dosent work as HAVING CLAUSE acts like WHERE CLAUSE in the absence of GROUP BY CLAUSE.
    Thanks & regards

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    This query will tell you which department has the employee with the highest salary. But it won't tell you anything about who has that salary or what the highest salary.

    Code:
    SELECT deptno
      FROM emp 
     WHERE sal = 
         ( SELECT MAX(sal)
             FROM emp );
    If you want to use the having clause then you need to specify group by and use the having clause to compare aggregate values like count.

    Code:
    SELECT deptno, COUNT(*)
      FROM emp
     GROUP BY deptno
    HAVING COUNT(*) > 1
     ORDER BY 1;
    this space intentionally left blank

  3. #3
    Join Date
    Jul 2009
    Posts
    10
    Quote Originally Posted by gandolf989 View Post
    This query will tell you which department has the employee with the highest salary. But it won't tell you anything about who has that salary or what the highest salary.
    Code:
    SELECT deptno
      FROM emp 
     WHERE sal = 
         ( SELECT MAX(sal)
             FROM emp );
    If you want to use the having clause then you need to specify group by and use the having clause to compare aggregate values like count.
    Code:
    SELECT deptno, COUNT(*)
      FROM emp
     GROUP BY deptno
    HAVING COUNT(*) > 1
     ORDER BY 1;
    Thanks Gandolf for replying i want some clarification from the answer
    Is it mandatory to use HAVING clause we need to specify GROUP BY clause.And is it mandatory to use only aggregate function in HAVING CLAUSE cant we use normal column ex: GROUP BY deptno HAVING deptno=10

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Cool Having where?

    To state it in simple terms: HAVING is the equivalent of the WHERE clause except it is applied after the query is executed and it filters the query results.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width