how to use ROWNUM to retrieve a row?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: how to use ROWNUM to retrieve a row?

  1. #1
    Join Date
    May 2001
    Posts
    285

    Question

    Hi,

    I was trying to use ROWNUM to retrieve a row but got no luck. What I did was:

    1. Find the max ROWNUM by doing

    select max(ROWNUM) from callhistory;

    MAX(ROWNUM)
    -----------
    1414980

    2. Use this ROWNUM to retrieve a row

    select calldate from callhistory where rownum = 1414980;

    no rows selected

    Can you tell me why it's not working?

    Thanks!

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    ronwum can be used with '=' sign only when using rownum=1, else you need to use rownum<=value, which will give you all the "value" first lines ...

    but the good question to ask is : why do you want to use rownum, it only gives you the order in which the lines are retrieved by your query, what exactly do you want to do ???

  3. #3
    Join Date
    May 2001
    Posts
    285
    Thanks pipo!

    My migration of a big CallHistory table from SQL Server to Oracle was failed in the middle(out of memory) -- there are totally 12 million rows need to be migrated while only 10% of them was moved over.

    I created a 2nd table in SQL Server which is the same as the original one except that it was grouped by CallDate (which I think that will make the CallDate column to be ordered). I was trying to use ROWNUM to find out what the last CallDate that has been migrated to Oracle, so I can just pick up from there.

    Does that make sense to you?

  4. #4
    Join Date
    Jun 2001
    Posts
    316
    go get a Nth record do this

    select * from table1 where rownum=1 and rowid NOT IN
    (SELECT rowid FROM table1 WHERE rownum < 12345);


  5. #5
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    in such a case, I'd try to find a number column (an id for example) in the table, and then migrate the rows block by block using a modulo function.
    for example let's say you have an id column which is a number, I'd first migrate all rows which id ends with a 1 (that is to say mod(id,10)=1), then all lines which id ends with 2, 3, and so on ... that way you only manipulate about 10% of the lines each time and you won't saturate your memory ...
    note that if it's still too much, you can use a number greater than 10 in your modulo function, that will reduce the number of lines in each "block" ...

  6. #6
    Join Date
    May 2001
    Posts
    285
    I looked at the table's data, and none of the NUMBER columns' data has a nice distribution :-( So I will just 'chop' the big table into a couple of smaller managable pieces, and migrate them one at a time.

    Thanks again for your help!

Posting Permissions

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



Click Here to Expand Forum to Full Width