Show department name and department number only once
Hi, experts.
I need to figur out following problem:
Write a SQL statement, which shows department_id, department_name, employee_id, last_name from the employees and departments tables. Show the department name and department number only once for each given department making sure that your data is sorted.
Output like this:
10 Administration 200 Whalen
20 Marketing 201 Hartstein
202 Fay
30 Purchasing 114 Raphaely
115 Khoo
116 Baida
Thanks a lot.
Orlando
Re: Show department name and department number only once
Try this oracle query,
[B][COLOR="RoyalBlue"] select decode (empno,(select empno from emp e1 where sal = (select max(sal) from emp e2 where e2.deptno = e.deptno) and rownum=1),d.deptno) dep,
decode (to_char(empno),(select to_char(empno) from emp e1 where sal = (select max(sal) from emp e2 where e2.deptno = e.deptno) and rownum=1),d.dname) depname,
e.empno,
e.ename
from dept d,emp e
where d.deptno=e.deptno
order by d.deptno,dep;[/COLOR][/B]
Re: Show department name and department number only once
Thanks James,
I was having the same question.
However I tried your code, but it doesnt display all the departments.
I have departments from 10 till 270.
Kindly suggest
Thanks
Re: Show department name and department number only once
What if in case there are more than one employee in a single department then how do you want your output to be??
And your sample output that you require that you have shown in your post is not sorted by dept_no.You want them to sort with by dept_no right?
Re: Show department name and department number only once
Hi Amit,
The sample output should be something like this:
10 Admiistration 201 Whalen
206 Harnstein
20 Marketing 156 Harvey
150 De Haal
30 Sales 120 King
179 Kochhar
180 Raja
and so on.
Yes sorting should be done with department number
But James query doesnt pull out all the departments.
Thanks
Re: Show department name and department number only once
Try this query:
[code]
SQL> SELECT DECODE(rn,1,deptno,NULL) deptno,DECODE(rn,1,deptname,NULL) deptname,emp_no,empname FROM
2 (SELECT x.deptno,y.deptname,x.emp_no,x.empname,row_number() OVER(PARTITION BY x.deptno ORDER BY
x.deptno) rn FROM emp x,dept y where x.deptno = y.deptno);
DEPTNO D EMP_NO EMPNAME
---------- - ---------- ----------------------------------------------------------------------------
10 A 2 BCD
1 a
1 a
20 B 2 ABCD
1 a
1 a
6 rows selected.
SQL>
[/code]
Re: Show department name and department number only once
Infact here is one more that display all the departments even if there are no employees in the department:
[code]
SQL> ed
Wrote file afiedt.buf
1 SELECT DECODE(rn,1,deptno,NULL) deptno,DECODE(rn,1,deptname,NULL) deptname,emp_no,empname FROM
2 (SELECT y.deptno,y.deptname,x.emp_no,x.empname,row_number() OVER(PARTITION BY y.deptno ORDER BY
3* y.deptno) rn FROM emp x,dept y where x.deptno(+) = y.deptno)
SQL> /
DEPTNO D EMP_NO EMPNAME
---------- - ---------- ----------------------------------------------------------------------------
10 A 2 BCD
1 a
1 a
20 B 2 ABCD
1 a
1 a
30 C
40 D
60 E
70 F
10 rows selected.
SQL>
[/code]
Re: Show department name and department number only once
Thanks Amit..!!
That works....