[jay_pink_elephant]

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
;

[iinfi]

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.

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

[jay_pink_elephant]

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

[iinfi]

hi
glad to know it helped you.
there will definitely be a better way to solve it.
can some experienced person post plz.
thanks