Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2010
    Posts
    26
    Rep Power
    0

    Post Rownum in oracle?

    I am using rownum in oracle but it not work fine.

    Record in Test Table is 10
    e.g select * from test where rownum<=5;

    it display 5 records.
    but


    e.g select * from test where rownum>1;
    then no record found is display.

    Help to solve this problem
    Thank in Advance..

  2. #2
    DarrylBurke's Avatar
    DarrylBurke is offline Member
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,188
    Rep Power
    19

  3. #3
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,757
    Rep Power
    19

    Default

    Also read the link at the bottom of that page ("On a table's first five rows").
    Because your first query above is essentially saying "give me five rows from the test table". It can return different results at different times.
    Last edited by Tolls; 06-22-2011 at 11:21 AM. Reason: Fix first sentence

  4. #4
    Join Date
    Mar 2010
    Posts
    26
    Rep Power
    0

    Default First 5 record of test table

    Quote Originally Posted by Tolls View Post
    Also read the link at the bottom of that page ("On a table's first five rows").
    Because your first query above is essentially saying "give me five rows from the test table". It can return different results at different times.
    following record for test table for first query...
    1
    2
    3
    4
    5

  5. #5
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,757
    Rep Power
    19

    Default

    And?
    What happens if you delete a row?
    What happens if you add 100 rows?
    Oracle can rejig the layout of rows in its memory as and when it feels a need.
    Oracle does not guarantee the order rows are returned in without an ORDER BY statement, so that same query is essentially picking any 5 rows from that table.

  6. #6
    DarrylBurke's Avatar
    DarrylBurke is offline Member
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,188
    Rep Power
    19

    Default

    You seem to have missed the point that rownum applies to the row number in the query result, not to the row number in the table.

    Why do people have to repeat here what's expressed very clearly in the linked web page?

    db

  7. #7
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,757
    Rep Power
    19

    Default

    Quote Originally Posted by DarrylBurke View Post
    You seem to have missed the point that rownum applies to the row number in the query result, not to the row number in the table.

    Why do people have to repeat here what's expressed very clearly in the linked web page?

    db
    Indeed I don't think there is a concept of row number in Oracle (or any other RDBMS?) for how the data is stored. It's purely a result set construct, as you've said.

  8. #8
    DarrylBurke's Avatar
    DarrylBurke is offline Member
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,188
    Rep Power
    19

    Default

    Visual FoxPro has a RECNO() function that returns the row number (record number, in VFP parlance) in the data table. So in VFP you can
    Java Code:
    SELECT * FROM Test WHERE RECNO() > 1
    db

  9. #9
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,757
    Rep Power
    19

    Default

    Maybe I should have said "real RDBMS"...;)

  10. #10
    Join Date
    Mar 2010
    Posts
    26
    Rep Power
    0

    Default

    select * from (select rownum r ,max_manual_per from test) where r >1;
    this query work fine
    so what mechanism for rownum concept in oracle...
    can u explain me if u know....

  11. #11
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,757
    Rep Power
    19

    Default

    That works because the subquery has numbered the rows.
    SELECT * FROM test WHERE rownum > 1 will return nothing.

    And we have explained the concept.
    Rownum is an identifier applied to the rows of a resultset.

  12. #12
    Join Date
    Mar 2010
    Posts
    26
    Rep Power
    0

    Default

    but when we check rownum>1 then no row selected
    but when we check rownum = 1 then one record found
    so what is concept for that

  13. #13
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,757
    Rep Power
    19

    Default

    Since rownum is something assigned as the resultset is built, then nothing can have a rownum > 1, since the first row is rejected each time.

  14. #14
    Join Date
    Mar 2010
    Posts
    26
    Rep Power
    0

    Default

    thank for answer....

  15. #15
    DarrylBurke's Avatar
    DarrylBurke is offline Member
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,188
    Rep Power
    19

    Default

    Quote Originally Posted by Tolls View Post
    Since rownum is something assigned as the resultset is built, then nothing can have a rownum > 1, since the first row is rejected each time.
    That's clear from the link I posted at #2, which I found through Google and vetted to make sure it had a clear, concise explanation.

    No more links for jatinkansagara, it's nothing but a waste of time.

    db

Similar Threads

  1. Oracle VM=Sun VM after oracle bought Sunmicrosystem?
    By jackiewu09 in forum Advanced Java
    Replies: 0
    Last Post: 05-16-2011, 10:28 PM
  2. Replies: 2
    Last Post: 05-11-2011, 09:02 AM
  3. new to oracle
    By emmett01 in forum JDBC
    Replies: 4
    Last Post: 11-15-2009, 06:16 AM
  4. connection with Oracle 10g
    By Somitesh Chakraborty in forum New To Java
    Replies: 5
    Last Post: 11-20-2008, 07:19 PM
  5. Replies: 0
    Last Post: 08-27-2008, 09:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •