sql querying

Question

Post reply

sql querying

harikolluri

SSC Veteran

Points: 233
More actions
April 2, 2003 at 1:02 pm

#101426

Hi
Good afternoon everyone.
Can u please help me out in this issue.
I have a table with 103 k rows.I wanted to select the first 25k rows and will do something(like inserting this into one more table or updating the value of one of the column) on that and then i want to get the next 25 k rows on the next execution of the sql statement,Means 25001 to 50000 rows.I will again do something on these rows and will try to fetch the next 25k rows.
like this i will get first 100k rows in 4 steps and the last 3k in the 5th step.
For this i need a sqlserver query or a stored procedure.
finally it's meant like
select top 25000 * from table a
select 25001 to 50000 * from table a
select 50001 to 75000 * from table a
..........
until i rach the last record
Thanks in Adavance
Edited by - harikolluri on 04/02/2003 1:17:30 PM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

Basically, you need something like this to return rows 25001 to 50000:

select top 25000 * from table a

where pk > (select max(pk) from (select top 25000 pk from table order by pk asc))

order by pk asc

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

sahana SSCrazy Points: 2530 More actions · Answer 2

It's not the way I required,If I execute the sql statement, In the first execution it should return first 25k rows and in the next execution it should return the next 25k(25001 to 50k), and for the 3rd execution it should return the next 25k(50001 to 75k),likewise untill the last record is fetched.Hope,u understand my problem.

sahana SSCrazy Points: 2530 More actions · Answer 3

Hi Chris

Thanks for ur help,I appreciate ur efforts over my prblem.

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 4

Solution needs some more work done on it

I used a table Item with PK(Item_No).

Also need some stress/load testing

Declare @From int,@To int,@c varchar(1000)

Select @From=900,@To=901

Set @C='Select ff.* from (Select top '+

Cast(@To-@From+1 as varchar(10))+

' f.* from (Select top '+

Cast(@To as varchar(10))+

' item_no from Item order by item_no) as f(Item_no) Order by item_no desc) as ff(Item_No) Order by ff.item_no'

Exec (@C)

sahana SSCrazy Points: 2530 More actions · Answer 5

sahana

SSCrazy

Points: 2530

April 2, 2003 at 3:44 pm

#452926

Thank u very much.It's working fine.

sahana SSCrazy Points: 2530 More actions · Answer 6

Hi i have one more question, with the above query I am getting the values in @C variable.Then how to get the reast of the columns for that value.I,e if its employee number then how to get the empname,sal fields corresponding to that numbers.

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 7

You may need to look at sp_executesql

See SQL Server Books Online for 'sp_executesql'

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 8

Prev pos refer to further enhancement.

To get other related fields :

Declare @From int,@To int,@c varchar(1000)

Select @From=900,@To=901

Set @C='Select ff.item_no,ii.Short_Descr from (Select top '+

Cast(@To-@From+1 as varchar(10))+

' f.* from (Select top '+

Cast(@To as varchar(10))+

' item_no from Item order by item_no) as f(Item_no) Order by item_no desc) as ff(Item_No)

Inner Join Item as ii on ii.Item_no=ff.item_no'

Exec (@C)

jmadren SSCrazy Points: 2055 More actions · Answer 9

Chris's solution works just fine. Maybe you didn't understand how to use it. The entire sequence you are looking for would be:

--1st chunk of 25000 rows

select top 25000 * from table a

--2nd chunk of 25000 rows

select top 25000 * from table a

where pk > (select max(pk) from (select top 25000 pk from table order by pk asc))

order by pk asc

--3rd chunk of 25000 rows

select top 25000 * from table a

where pk > (select max(pk) from (select top 50000 pk from table order by pk asc))

order by pk asc

--4th chunk of 25000 rows

select top 25000 * from table a

where pk > (select max(pk) from (select top 75000 pk from table order by pk asc))

order by pk asc

--last chunk of rows (remainder, < 25000)

select top 25000 * from table a

where pk > (select max(pk) from (select top 100000 pk from table order by pk asc))

order by pk asc

A benefit of this method is the pk can be a number or a character field. And it doesn't have to be the Primary Key, just a unique, non-null field.

Jay Madren

bruce_reid SSCrazy Points: 2419 More actions · Answer 10

Hey i wanted to still enhance this requirement,Now my requirement is that I have the table two columns.one is number with values of 1 or 2 and the name with varchar datatype.

so i need to pick up the set of rows in 25 k steps.

Anyway my basic requirement is to get the all the rows in 25k steps from 103k rows,event it has got only one column with varchar datatype or a number column with a single value of '1' in all the columns.

i want to get first 25 k rows,then next 25 k rows,then next 25k....

Thanks in advance...