[PHP-DB] [plain SQL Problem] Group By
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 NameType - 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_IDEMP_AGE EMP_NAME - --- -- 1 1 32john 2 1 19 mike 3 1 40 alex 4 2 42 norman 5 2 33 jason 6 2 20bert 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 40alex 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_NAMEYOUNGEST_AGE OLDEST_AGE -- marketing 2042 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
Re: [PHP-DB] [plain SQL Problem] Group By
Em Wed, 28 May 2003 14:54:19 +0200 Sapporo [EMAIL PROTECTED] escreveu: Hi, sorry if this is off topic. I hope you don't mind a plain SQL question here. Maybe this works... SELECT D.dept_name, MIN(E1.emp_age) AS YOUNGEST_AGE, MAX(E2.emp_age) AS OLDEST_AGE, E1.emp_name AS YOUNGEST_NAME, E2.emp_name AS OLDEST_NAME 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, E1.emp_name, E2.emp_name -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
