| select query [message #9459] |
Wed, 12 November 2003 06:17  |
samm khan
Messages: 9
Registered: November 2003
|
Junior Member |
|
|
hi all,
my problem is to select all the location of the deptarment where highest no. of employees work.
thanks in advance
|
|
|
|
| Re: select query [message #9460 is a reply to message #9459] |
Wed, 12 November 2003 06:45  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
Here is one way. It requires at least version 8.1.6:SQL> SELECT *
2 FROM emp e
3 , dept d
4 WHERE e.deptno = d.deptno
5 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ---------- -------------- -------------
7369 SMITH CLERK 7902 12/17/1980 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 2/20/1981 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 2/22/1981 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 4/2/1981 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 9/28/1981 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 5/1/1981 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 6/9/1981 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 12/9/1982 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 11/17/1981 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 9/8/1981 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 1/12/1983 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 12/3/1981 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 12/3/1981 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 1/23/1982 1300 10 10 ACCOUNTING NEW YORK
14 rows selected.
SQL> <font color=blue>SELECT SUBSTR(x.max_val,7) location</font>
2 <font color=blue>, TO_NUMBER(SUBSTR(x.max_val,1,6)) qty</font>
3 <font color=blue>FROM (SELECT MAX(TO_CHAR(grpd.emp_qty,'fm000000')</font>
4 <font color=blue> || grpd.loc) max_val</font>
5 <font color=blue> FROM (SELECT DISTINCT</font>
6 <font color=blue> d.loc</font>
7 <font color=blue> , COUNT(e.ename)</font>
8 <font color=blue> OVER (PARTITION BY e.deptno) emp_qt</font>
9 <font color=blue> FROM emp e</font>
10 <font color=blue> , dept d</font>
11 <font color=blue> WHERE e.deptno = d.deptno) grpd) x</font>
12 /
LOCATION QTY
-------------- ----------
CHICAGO 6SQL> INSERT INTO emp VALUES (7619,'BLACK','CLERK',7369,TO_DATE('19811006','YYYYMMDD'),500,TO_NUMBER(NULL),20);
1 row created.
SQL> INSERT INTO emp VALUES (7737,'YAROLEM','ANALYST',7566,TO_DATE('19820630','YYYYMMDD'),2100,TO_NUMBER(NULL),20);
1 row created.
SQL> SELECT *
2 FROM emp e
3 , dept d
4 WHERE e.deptno = d.deptno
5 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ---------- -------------- -------------
7369 SMITH CLERK 7902 12/17/1980 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 2/20/1981 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 2/22/1981 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 4/2/1981 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 9/28/1981 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 5/1/1981 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 6/9/1981 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 12/9/1982 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 11/17/1981 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 9/8/1981 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 1/12/1983 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 12/3/1981 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 12/3/1981 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 1/23/1982 1300 10 10 ACCOUNTING NEW YORK
7619 BLACK CLERK 7369 10/6/1981 500 20 20 RESEARCH DALLAS
7737 YAROLEM ANALYST 7566 6/30/1982 2100 20 20 RESEARCH DALLAS
16 rows selected.
SQL> <font color=blue>SELECT SUBSTR(x.max_val,7) location</font>
2 <font color=blue>, TO_NUMBER(SUBSTR(x.max_val,1,6)) qty</font>
3 <font color=blue>FROM (SELECT MAX(TO_CHAR(grpd.emp_qty,'fm000000')</font>
4 <font color=blue> || grpd.loc) max_val</font>
5 <font color=blue> FROM (SELECT DISTINCT</font>
6 <font color=blue> d.loc</font>
7 <font color=blue> , COUNT(e.ename)</font>
8 <font color=blue> OVER (PARTITION BY e.deptno) emp_qt</font>
9 <font color=blue> FROM emp e</font>
10 <font color=blue> , dept d</font>
11 <font color=blue> WHERE e.deptno = d.deptno) grpd) x</font>
12 /
LOCATION QTY
-------------- ----------
DALLAS 7
SQL>
A.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
