Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2008
    Posts
    6

    Unanswered: MIN MAX subqueries

    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

    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. #2
    Join Date
    May 2006
    Posts
    42
    hi,

    i m only a novice in Oracle and i tried something similar to what your original query is.
    i took the employees table of the HR schema which is created by default in Oracle when it is installed.

    what i did is to 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:
    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;
    i created 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 you can try to create a couple of global temporary tables may be.

    i dont know whether this helps. if it does gr8.

    thanks

    added: or creating two views mite also help

  3. #3
    Join Date
    Oct 2008
    Posts
    6
    thanks for that, yeh it worked. i made 2 separate tables and joined them. i wanted to do it in one execution without making tables or views but it will do

  4. #4
    Join Date
    May 2006
    Posts
    42
    hi
    glad to know it helped you.
    there will definitely be a better way to solve it.
    can some experienced person post plz.
    thanks

Posting Permissions

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