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!

MIN MAX subqueries

Discussion in 'SQL PL/SQL' started by jay_pink_elephant, Oct 16, 2008.

  1. jay_pink_elephant

    jay_pink_elephant Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    hi, im trying to list youngest and eldest agents by location using min and max sub-queries. Problem is agents appear in both columns regardless of age:

    LOCATION YOUNGEST ELDEST
    Birmingham Pelton Magoon Pelton Magoon
    Birmingham Tranter Toucey Tranter Toucey
    Bournemouth Lily Beaufort Lily Beaufort
    Bournemouth Ned Killin Ned Killin
    Brighton Joanna Oakham Joanna Oakham
    Brighton Newell Marmaduke Newell Marmaduke

    Should look like:

    LOCATION YOUNGEST ELDEST
    Birmingham Tranter Toucey Pelton Magoon
    Bournemouth Ned Killin Lily Beaufort
    Brighton Newell Marmaduke Joanna Oakham

    Heres the script, please help

    Code (Text):
    SELECT
    l.description Location,
    a.first_name||' '||a.last_name Youngest,
    a.first_name||' '||a.last_name Eldest
    FROM
    locations l
    INNER JOIN
    agents a
    ON
    l.location_id=a.location_id
    WHERE
    a.birth_date =
    (
    SELECT
    MAX(a2.birth_date) as Youngest
    FROM
    agents a2
    WHERE
    a.location_id = a2.location_id)
    OR
    a.birth_date =
    (
    SELECT
    MIN(a3.birth_date) as Eldest
    FROM
    agents a3
    WHERE
    a.location_id = a3.location_id
    )
    GROUP BY
    l.description,
    a.first_name||' '||a.last_name
    ORDER BY
    l.description
    ;
     
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
  3. jay_pink_elephant

    jay_pink_elephant Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    i made 2 separate tables and joined them. i wanted to do it in one execution without making tables or views but this worked

    Actually i was given this suggestion

    Take the employees table of the HR schema which is created by default in Oracle when it is installed.

    list the job_id, last_name (maxSalary),last_name(minSalary)
    i.e. list the job_id, last name of the person who earns the max salary in the department and also the last name of the person who earns the minimum salary in the department.

    Code (Text):

    create table temptb1 as
    (select egr.jd,e.last_name minimumSal,egr.mins from employees e,(select job_id jd,min(salary) mins,max(salary) maxs from employees group by job_id) egr
    where e.job_id = egr.jd and e.salary = egr.mins)

    create table temptb2 as
    (select egr.jd,e.last_name maximumSal,egr.maxs from employees e,(select job_id jd,min(salary) mins,max(salary) maxs from employees group by job_id) egr
    where e.job_id = egr.jd and e.salary = egr.maxs)

    select t1.jd, t1.MINIMUMSAL, t2.MAXIMUMSAL from temptb1 t1,temptb2 t2 where t1.jd=t2.jd;
     
    create two tables, one to list the job_id and the lastname of the person who earns the minimum salary in the department and another table to list the job_id and the lastname of the person who earns the max salary in the department.
    later another query to do a simple join.

    similarly try to create a couple of global temporary tables may be.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    By the way you can use the straight SQL to achieve the same implementing the same logic. ie, by creating the two inline views on the fly in SQL.

    You can use either of one of the below option . One uses Correlated subquery while other uses simple join with inline views.

    Code (Text):

    SQL> select distinct e.DEPTNO  ,
      2         minsal.sal MIN_SAL  ,
      3         ( select MAX(ENAME) FROM emp e1
      4           where e1.deptno = e.deptno
      5           and   e1.sal    =  minsal.sal )  MIN_SAL_EMP,
      6           maxsal.sal MAXSAL,
      7         ( select MAX( ENAME) FROM emp e2
      8           where e2.deptno = e.deptno
      9           and   e2.sal    =  maxsal.sal )  MAX_SAL_EMP
     10  from emp e  ,
     11       ( SELECT DEPTNO ,MIN(SAL) sal
     12         FROM EMP
     13         group by DEPTNO ) minsal ,
     14       ( SELECT DEPTNO, MAX(SAL)  sal
     15         FROM EMP
     16         group by DEPTNO ) maxsal
     17  where e.deptno = minsal.deptno
     18  and   minsal.deptno = maxsal.deptno;

        DEPTNO    MIN_SAL MIN_SAL_EM     MAXSAL MAX_SAL_EM
    ---------- ---------- ---------- ---------- ----------
            20        800 SMITH            3000 SCOTT
            10       1300 MILLER           5000 KING
            30        950 JAMES            2850 BLAKE

    SQL>
     
    Code (Text):


    SQL> select minsal.deptno DEPTNO ,
      2         minsal.sal MIN_SAL,
      3         minsal.ename MIN_SAL_EMP,
      4         maxsal.sal MAX_SAL,
      5         maxsal.ename MAX_SAL_EMP
      6  from
      7        (SELECT DEPTNO ,sal, max(ename) ename
      8         FROM EMP e
      9         where sal =( select MIN (SAL)
     10                     from EMP e1
     11                     where e1.DEPTNO = e.DEPTNO)
     12         group by DEPTNO, sal ) minsal,
     13         (SELECT DEPTNO ,sal ,max(ename) ename
     14         FROM EMP e
     15         where sal =( select MAX (SAL)
     16                     from EMP e1
     17                     where e1.DEPTNO = e.DEPTNO)
     18         group by DEPTNO,sal )maxsal
     19  where minsal.deptno = maxsal.deptno
     20  ;

        DEPTNO    MIN_SAL MIN_SAL_EM    MAX_SAL MAX_SAL_EM
    ---------- ---------- ---------- ---------- ----------
            10       1300 MILLER           5000 KING
            20        800 SMITH            3000 SCOTT
            30        950 JAMES            2850 BLAKE

    SQL>
     
    incorpoate this logic as per your requirement .