I have next problem.
I have table EMP with columns
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)).
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.
[Edited by PSoni on 04-05-2001 at 02:57 AM]
04-05-2001, 03:05 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.
04-05-2001, 10:22 PM
04-06-2001, 08:01 AM
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?