-
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,
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
-
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|