Rownum and Order by

Posted on 2005-05-05
Last Modified: 2008-02-01
We have a nightly batch process that sqlldr records into a staging table.  When we process the file, we break up the file into chunks i.e. 1000 records at a time.  so our query looks like 'select * from stg where rownum < 1000 order by account_number'.
On occasion the process will skip rows, i.e. it will process 1-150, skip 50 records, and then continue 200-999.  What causes this?
Question by:slim7
    LVL 12

    Accepted Solution

    Don't use rownum. It's a pseudo column and should not be used for purposes like yours.

    Instead,a row_number analytic function...

    --- First 1000 rows based on order by clause
    select col1,col2,col3
    select a.col1,a.col2,a.col3, row_number() over (order by a.col1) as rn
    from mytable a
    where rn < 1000;

    --- Next 1000 rows
    select col1,col2,col3
    select a.col1,a.col2,a.col3, row_number() over (order by a.col1) as rn
    from mytable a
    where rn between 1001 and 2000;
    LVL 4

    Expert Comment

    thw wrong place of the rownum. rownum is assigned by oracle just before prder by clause to be handled.
    so, rewrite the query:

    select * from
      select * from stg where order by account_number
    where rownum < 1000;

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now