Thread: 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

Try this oracle query,

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;

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

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?

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

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>

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>

Thanks Amit..!!

That works....