3 Replies Latest reply: Aug 8, 2008 12:35 AM by 587294 RSS

    Fetch First

    587294
      Hi all,
      I am using Oracle 9.
      I am converting my database from DB2 to Oracle.
      I had one Query with Fetch First like this:

      SELECT EMPNO, LASTNAME, FIRSTNME, SALARY
      FROM DSN8710.EMP
      ORDER BY SALARY DESC
      FETCH FIRST 4 ROWS ONLY;

      That FETCH FIRST giving error in Oracle, Can any one tell me the remedy for this.
      Thanks
        • 1. Re: Fetch First
          NicloeiW
          i dont think fetch first is there in oracle


          if u want determine topd 4 highest salary here is one such example
          which gives top 3 highest salary as per deptno
          SQL> select ename
            2       , deptno
            3       , sal
            4       , rn
            5    from (select ename
            6               , deptno
            7               , sal
            8               , Row_Number() over (partition by deptno
            9                                        order by sal desc
          10                                   ) rn
          11            from emp
          12         )
          13   where rn <= 3
          14  /

          ENAME          DEPTNO        SAL         RN
          ---------- ---------- ---------- ----------
          KING               10       5000          1
          CLARK              10       2450          2
          MILLER             10       1300          3
          SCOTT              20       3000          1
          FORD               20       3000          2
          JONES              20       2975          3
          BLAKE              30       2850          1
          ALLEN              30       1600          2
          TURNER             30       1500          3
          • 2. Re: Fetch First
            337410
            Hi,

            try this http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
            • 3. Re: Fetch First
              587294
              Thank you very much to both of you