problem with row_number - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

problem with row_number

Author

Topic

CrazyT
Yak Posting Veteran

73 Posts

Posted - 2010-04-16 : 15:02:13

i have a table with 2 columns

rowid, InProgress

i am grabbing the rows in groups of 100 using row_number

i do it by getting the count where InProgress
= null and then dividing by 100 (number of rows i want)

select count(*)
from temp
where inprogress is null

so for 10k records im iterating it 100 times

so i loop through and pass 0-100, 200-300, and so on

works fine if i go all the way through non stop

when i stop it and restart it doesnt get all the rows

so 5k are done (inprogress no null) 0-100, 200-300 50 more times

the below query doesnt always get me all the rows

SELECT ID, InProgress
FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID) AS Row, ID,InProgress FROM temp WHERE InProgress is null ) AS temp
WHERE InProgress is null and Row between @Start and @END

its like the counts are off.

can someone see what Im missing

byrdzeye
Starting Member

14 Posts

Posted - 2010-04-16 : 16:26:51

0-100, 200-300, and so on...

Unless thats a typo, you are missing 101-199.

CrazyT
Yak Posting Veteran

73 Posts

Posted - 2010-04-16 : 16:47:34

type == increments of a 100 on the startrow

CrazyT
Yak Posting Veteran

73 Posts

Posted - 2010-04-16 : 17:14:01

well i found out why it isnt giving me the right rows -- not sure how to fix it.

as i pass in my start rows and it updates the table the result changes for the number of nulls, which changes the row_count if i have WHERE InProgress is null in the temp -- each time it iterates.

if i take it out it give me the row number from the whole set not the row number from just the null ones.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-16 : 17:19:21

Instead of telling us what you ARE doing, can you explain what you WANT to do?

Sounds like you are simply doing some batch updates or something..?

CrazyT
Yak Posting Veteran

73 Posts

Posted - 2010-04-17 : 18:00:14

what i want to do is take a large set and break it into smaller set to pass off to thread

what Im doing is getting the total count, then passing the startrow/endrow for the required number of iterations.

if i start and completely finish it works fine.

if i have to stop the process middway i want to start up and complete the remaining records.

I get the remaining record count and start again passing the number of iterations into rownumber.

the problem is when i update the records and pass the next iteration i cant get the query with the row_count to work right. If i get where inprogress = null it adjusts the row_count every time it iterates because the records are updated as it loops.

what i want to do is grab all records that have inprogess - null and update them.

problem is as i update them the row_number row changes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 04:34:44

quote:
Originally posted by CrazyT

what i want to do is take a large set and break it into smaller set to pass off to thread

what Im doing is getting the total count, then passing the startrow/endrow for the required number of iterations.

if i start and completely finish it works fine.

if i have to stop the process middway i want to start up and complete the remaining records.

I get the remaining record count and start again passing the number of iterations into rownumber.

the problem is when i update the records and pass the next iteration i cant get the query with the row_count to work right. If i get where inprogress = null it adjusts the row_count every time it iterates because the records are updated as it loops.

what i want to do is grab all records that have inprogess - null and update them.

problem is as i update them the row_number row changes

Why not store the value somewhere in case it stops miodway and then take the value from temp table and resume

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources