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!

how to display last 5 rows reg

Discussion in 'SQL PL/SQL' started by laxman, Jan 8, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear ALL,
    I am trying to get the last 5 row from one colomn using this query, but unable to get the output. is this query logic is correct

    SELECT lastmoddate
FROM (select lastmoddate from A ORDER BY lastmoddate DESC) B
WHERE rownum <= 5
ORDER BY rownum DESC;

    Note:lastmoddate is of date datatype.

    Thanks
    Laxman
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,349
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is not the forum, it's where informative articles regarding Oracle are posted. You've been posting long enough (67 posts thus far) to know the difference. That being said why did you not post the output you're seeing? The query works just fine:

    Code (SQL):
    SQL> WITH a AS (
      2     SELECT sysdate -20 lastmoddate
      3     FROM dual
      4     UNION ALL
      5     SELECT sysdate -17 lastmoddate
      6     FROM dual
      7     UNION ALL
      8     SELECT sysdate -16 lastmoddate
      9     FROM dual
     10     UNION ALL
     11     SELECT sysdate -13 lastmoddate
     12     FROM dual
     13     UNION ALL
     14     SELECT sysdate -10 lastmoddate
     15     FROM dual
     16     UNION ALL
     17     SELECT sysdate -8 lastmoddate
     18     FROM dual
     19     UNION ALL
     20     SELECT sysdate -7 lastmoddate
     21     FROM dual
     22     UNION ALL
     23     SELECT sysdate -5 lastmoddate
     24     FROM dual
     25     UNION ALL
     26     SELECT sysdate -3 lastmoddate
     27     FROM dual
     28  )
     29  SELECT lastmoddate FROM (SELECT lastmoddate FROM A ORDER BY lastmoddate DESC) B WHERE rownum <= 5 ORDER BY rownum DESC;

    LASTMODDA
    ---------
    29-DEC-09
    31-DEC-09
    01-JAN-10
    03-JAN-10
    05-JAN-10

    SQL>
    If you won't post your results no one here can see what you do and, as a result, cannot help you resolve your issue.

    Admins, please move this to the proper forum.
     
  3. nktech

    nktech Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Mississauga, Canada
    Hi Laxman,

    I replicated the same query on Emp table under scott. Here is the query:

    select hiredate from ( select hiredate from scott.emp order by hiredate desc)
    where rownum<=5
    order by rownum desc

    it is working fine and giving me the result.

    Thanks.

    Neeraj