DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: CURSOR

Hybrid View

  1. #1
    Join Date
    Dec 2000
    Posts
    3
    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

  2. #2
    Join Date
    Feb 2001
    Posts
    125
    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]

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  4. #4
    Join Date
    Dec 2000
    Posts
    3
    Thanks,
    It works

  5. #5
    Join Date
    Aug 2000
    Posts
    462
    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
  •  


Click Here to Expand Forum to Full Width