[plain SQL Problem] Group By

From: Date: Wed, 28 May 2003 12:54:19 +0000
Subject: [plain SQL Problem] Group By
Groups: php.db 
Request: Send a blank email to php-db+get-28116@lists.php.net to get a copy of this message
Hi, 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.

« previous php.db (#28116) next »