[plain SQL Problem] Group By
| From: | Sapporo | 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 deptAnd here's the result I'd like to produce:Name Type ----------------- ------------ DEPT_ID NUMBER(10)DEPT_NAME VARCHAR2(20) SELECT * FROM dept;DEPT_ID DEPT_NAME ------- -------------------- 1 sales 2 marketingDESC empName 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
???
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_nameWhat 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.DEPT_NAME YOUNGEST_AGE OLDEST_AGE-------------------- ------------ ----------marketing 20 42 sales 19 40