CURSOR

Hello
I have next problem.
I have table EMP with columns
deptno number(3)
empno number(3)
dhire date
In this table I have for example 1000 rows.
I need to write cursor which will retrieve one row per deptno, if I have many rows with the same deptno than I need the latest one (max(dhire)).

For example if I have

deptno empno dhire
1 11 1.1.2000
1 12 1.2.2000
2 21 2.1.2000
3 31 3.1.2000
3 32 2.1.2000

then I need next rows:

deptno empno dhire
1 12 1.2.2000
2 21 2.1.2000
3 31 3.1.2000

If anyone has idea...

Help in advance

Hi,

try this


select deptno, empno, hiredate from emp
where (deptno,hiredate) in (select deptno,max(hiredate)
from emp group by deptno)
order by deptno
Hope this is ok.


Thanks,
P. Soni

[Edited by PSoni on 04-05-2001 at 02:57 AM]

Maybe this one...

SELECT e.deptno, MAX(e.empno) empno, e.dhire
FROM emp e,
(SELECT deptno, MAX(dhire) dhire FROM emp GROUP BY deptno) e1
WHERE e.deptno = e1.deptno
AND e.dhire = e1.dhire
GROUP BY e.deptno, e.dhire;

MAX(e.empno) in the main select is used in case you have more employees hired at the same date, so that the query returns only one of them.

HTH,

Thanks,
It works

jmodic,

Would your query consistently return the same employee if there are multiple employees hired on the same date in the same department? What if the table is reorganized or in some way altered in sequence (CTAS? Export/Import?)? Clearly you will get AN employee from the set defined by OCPDBA2001. I would just like to more completely understand the result of this query. Thanks?

OCP - is that a problem if true?