1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query builder

Discussion in 'SQL PL/SQL' started by keshav jain, Jul 10, 2014.

  1. keshav jain

    keshav jain Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    How can I retrieve a particular row.

    My problem i.e. I have a table and I want to fetch 5th row so How can I do it?
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    687
    Likes Received:
    137
    Trophy Points:
    830
    Location:
    Russian Federation
    It may depends on order rows ,if you used clause : order by ....

    For example :
    1)
    Code (SQL):

    SELECT * FROM (SELECT rownum  rn ,u.TABLE_NAME FROM user_tables  u ) WHERE rn = 5
     
    2)
    http://www.oracle-base.com/articles/misc/top-n-queries.php
    Code (SQL):


    SELECT * FROM (
    SELECT ROW_NUMBER( ) OVER(ORDER BY u.NUM_ROWS) rn  ,
           u.TABLE_NAME
    FROM user_tables  u
     
    ) WHERE rn = 5

     
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,467
    Likes Received:
    355
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Define 'fifth row'. Unless this is an Index-organized Table there is no order to the rows as it's a heap table. You need to order the data by your criteria to determine WHICH row is the 'fifth' row. Here is an example of how you might do that:


    Code (SQL):

    SQL>  SELECT empno, ename, job, sal, comm, hiredate
      2  FROM
      3  (SELECT empno, ename, job, sal, comm, hiredate, ROW_NUMBER() OVER (ORDER BY empno) rn
      4  FROM empdrop)
      5  WHERE rn=5
       6 /


         EMPNO ENAME      JOB              SAL       COMM HIREDATE
    ---------- ---------- --------- ---------- ---------- ---------
          7369 SMITH      CLERK            800            17-DEC-80


    SQL>