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!

How to exclude some one from a table

Discussion in 'SQL PL/SQL' started by cfoucault1, Jul 15, 2010.

  1. cfoucault1

    cfoucault1 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I need to calculate a highest salary and I need to exclude. Some one from this. How do I do that this is my code so far. I need to remove the owner who technically makes the highest salary for obvious reasons. How do I prevent him from showing up in my average.

    SELECT first_name,last_name, job_id, salary
    FROM employees
    WHERE salary=
    (SELECT max(salary)
    FROM employees);
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Try with

    Code (SQL):
    .... WHERE SALARY NOT IN ( ....)
     
  3. ashishb01

    ashishb01 Active Member

    Messages:
    13
    Likes Received:
    1
    Trophy Points:
    110
    Hi,
    What rajavu is saying would surely work... but NOT IN may not be a performance oriented optimized query. I think a simpler and better solution could be:
    Code (SQL):

    SELECT first_name,last_name, job_id, salary
    FROM employees
    WHERE salary<
    (SELECT MAX(salary)
    FROM employees);
     
    I am open to better solutions however
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Plesae post the create table statement. Knowing the columns in the table may present a better solution.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Let us presume this is the same EMPLOYEE table found in the demo HR schema:

    Code (SQL):
     
    EMPNO NUMBER(4)
    ENAME VARCHAR2(10)
    JOB VARCHAR2(9)
    MGR NUMBER(4)
    HIREDATE DATE
    SAL NUMBER(7,2)
    COMM NUMBER(7,2)
    DEPTNO NUMBER(2)
     
    Let us also presume that the MGR column is NULL for the President/Owner. A fairly simple and straightforward query emerges that should return the highest salary excluding the President's salary:

    Code (SQL):
     
    SELECT first_name, last_name, job_id, salary
    FROM
    (SELECT first_name,last_name, job_id, salary
    FROM employees
    WHERE mgr IS NOT NULL
    ORDER BY salary DESC)
    WHERE rownum = 1;