sorry if this is off topic. I hope you don't mind a plain SQL question here.
I know I can do what I want using subqueries (I'm on Oracle9i here), but I would really like to avoid them for performance reasons. Here's my example data:
> DESC dept Name Type ----------------- ------------ DEPT_ID NUMBER(10) DEPT_NAME VARCHAR2(20)
> SELECT * FROM dept; DEPT_ID DEPT_NAME ------- -------------------- 1 sales 2 marketing
> DESC emp Name Type ------------------------------ EMP_ID NUMBER(10) EMP_DEPT_ID NUMBER(10) EMP_AGE NUMBER(2) EMP_NAME VARCHAR2(20)
> SELECT * FROM emp; EMP_ID EMP_DEPT_ID EMP_AGE EMP_NAME --------- ----------- ---------- -------- 1 1 32 john 2 1 19 mike 3 1 40 alex 4 2 42 norman 5 2 33 jason 6 2 20 bert
And here's the result I'd like to produce:
> ???
DEPT_NAME YOUNGEST_AGE YOUNGEST_NAME OLDEST_AGE OLDEST_NAME
--------- ------------ ------------- ---------- -----------
marketing 20 bert 42 norman
sales 19 mike 40 alex
Here's what I have so far:
> SELECT D.dept_name, MIN(E1.emp_age) AS YOUNGEST_AGE, MAX(E2.emp_age) AS OLDEST_AGE
FROM dept D, emp E1, emp E2
WHERE E1.emp_dept_id = D.dept_id AND E2.emp_dept_id = D.dept_id
AND E1.emp_id <> E2.emp_id
GROUP BY D.dept_name
DEPT_NAME YOUNGEST_AGE OLDEST_AGE
-------------------- ------------ ----------
marketing 20 42
sales 19 40
What is missing ist the name of the younges and oldest employee, since I can't use aggregate functions to get at them.
Any help would be greatly appreciated!
Thanks, -sapporo.
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
