Tossing out certain result rows in a left join

Question

In DB2, using the following left join

select a.deptno, a.deptname, b.empno
from #dept a
left join #emp b
on a.deptno = b.workdept

on two tables, gets me a list like:

dpt  name         emp
----------------------
A01  ACCOUNTING   5001
A02  PAYROLL      NULL
A03  OPERATIONS   5003
A03  OPERATIONS   5004
A03  OPERATIONS   5007
A05  MAINTENANCE  NULL

but I want only the first instance of any dpt. Is there a way to code the left join to pull only the first occurrence, so that it would look like:

dpt  name         emp
----------------------
A01  ACCOUNTING   5001
A02  PAYROLL      NULL
A03  OPERATIONS   5003
A05  MAINTENANCE  NULL

Answer 1

did you try distinct/unique?

Answer 2

You can try something like

select a.deptno, a.deptname, min(b.empno)
from #dept a
left join #emp b
on a.deptno = b.workdept
group by a.deptno, a.deptname

Answer 3

select a.deptno, a.deptname, b.empno
from #dept a
left join #emp b
on a.deptno = b.workdept
group by a.deptno
having b.empno = min(b.empno)

Something like this should work.

Answer 4

It really depends on how you define "first". In the result set you've generated, the empno is essentially random. If the empno doesn't matter, why not leave it out? So:

SELECT DISTINCT a.deptno, a.deptname
FROM #dept aleft 
JOIN #emp bon a.deptno = b.workdept;

If the empno does matter, then you need to define what quality of empno is most important and test for it. So:

SELECT a.deptno, a.deptname, b.empno
FROM #dept aleft 
JOIN #emp bon a.deptno = b.workdept
GROUP BY a.deptno
HAVING  b.empno = some_criteria(b.empno);//where some_criteria is the appropriate function