Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    35

    Unanswered: get rownumber of a specific row in a table

    I would like to get the data between rownumbers 49 and 150. How can I do that?

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    Re: get rownumber of a specific row in a table

    Originally posted by durbhakula
    I would like to get the data between rownumbers 49 and 150. How can I do that?

    That is not really a good idea - Oracle does not pull data in a standard fashion- Just because it is in order in the table does not mean it will come back that way.

    You should add an order by statement to ensure the order, and then if you absolutely need only those rows do this:

    Code:
    SELECT * 
    FROM your_table
    where rownum between 49 and 150

    This is highly not recommended though as it is really bad database practice to use rownum as selection criteria like that.

    Just my 2 cents

  3. #3
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: get rownumber of a specific row in a table

    Originally posted by durbhakula
    I would like to get the data between rownumbers 49 and 150. How can I do that?
    select a from (
    select rownum rnum, a from table1
    where rownum < 151)
    where rnum > 48

    you can modify it to fit your case

  4. #4
    Join Date
    Feb 2004
    Posts
    35
    I executed exactly as you said.. but I got no results..But when I did

    select user_id, rownum from up_user;

    I got results..


    I do not understand....So, you mean to say that I cannot view any specific row's data??

  5. #5
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by durbhakula
    I executed exactly as you said.. but I got no results..But when I did

    select user_id, rownum from up_user;

    I got results..


    I do not understand....So, you mean to say that I cannot view any specific row's data??

    You can view a specific row's data, but using rownum is not a great idea.

    Oracle doesnt just pull the rows out exactly the way they look in the database. Sometimes yes - other times no. To pull them in the order you want, you need to add an order by statement.

    So lets say you have something in row 50. When you select data from the table without an order by statement, there's no guarantee that it would actually come up as the 50th row. And if you added another row, how would you know which row it actually was?

    A better way is to make sure you have a primary key (has to be unique) and pull the rows based on that primary key or some other criteria.

    You must have some criteria for wanting the rows between 50-150 right? They must represent something to you--- think about why only those rows should be selected, and you can put that criteria in your where statement.

  6. #6
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    using rownum could be dangerous or misleading if you don't understand it well.
    if you understand that rownum is nothing but rows processed by oracle then it will make things easier sometimes. Remember rownum is set for each row before order by in the same query/subquery.

  7. #7
    Join Date
    Feb 2004
    Posts
    35
    Thank You guys for the exlanation.

    Actually, I am not new to SQL. But, I never used something like this.....but, all of a sudden I got a request like this..And actually did not know if I could do..So, I was asking the question..

    Yeah, I have primary key..Finally I just select the rows based on my primary key and then massaged the data in an editor to get what I needed.

    OK..It's good when someone gives as a logical explanation..gives a better understanding..

    Thanks

  8. #8
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    The reason why you dont get rows by doing:

    select * from table1
    where rownum between 49 and 150

    is because rownum starts from 1 then increments 1 after each row, if you have no 1 then you cannot get 2 and so on.

    Only the following expressions make sense:
    rownum = 1
    rownum between 1 and n (n>=1)
    rownum < n (n>1)

  9. #9
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by lynden.zhang
    The reason why you dont get rows by doing:

    select * from table1
    where rownum between 49 and 150

    is because rownum starts from 1 then increments 1 after each row, if you have no 1 then you cannot get 2 and so on.

    Only the following expressions make sense:
    rownum = 1
    rownum between 1 and n (n>=1)
    rownum < n (n>1)
    Never tried getting anything between rows before, so I had no idea that wouldnt work

  10. #10
    Join Date
    Feb 2004
    Posts
    45
    Let me add my voice to those telling you not to look for data this way in Oracle. Ask for records meeting a criteria based on column values. If you ever venture into db_links, nested tables, and some other nasties, you will not get what you want.
    If you want to browse a sample of your data, there are tools that will let you do that (freetoad for instance).
    HTH
    Cliff
    It was working just 5 minutes ago - I promise !

Posting Permissions

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