• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1189
  • Last Modified:

Rownum and Order by

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?
0
slim7
Asked:
slim7
1 Solution
 
catchmeifuwantCommented:
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
from
(
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
from
(
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;
0
 
plamen73Commented:
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
)stg_ordered
where rownum < 1000;
------------------------------------------
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now