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

• Chris Hedgate

  One Orange Chip

  Points: 25041

  More actions

  April 2, 2003 at 2:38 pm

  #452917

  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

  April 2, 2003 at 2:55 pm

  #452920

  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

  April 2, 2003 at 2:57 pm

  #452921

  Hi Chris

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

• 5409045121009-7368

  SSCoach

  Points: 15740

  More actions

  April 2, 2003 at 3:26 pm

  #452924

  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

  April 2, 2003 at 3:44 pm

  #452926

  Thank u very much.It's working fine.

• sahana

  SSCrazy

  Points: 2530

  More actions

  April 2, 2003 at 4:06 pm

  #452930

  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

  April 2, 2003 at 4:09 pm

  #452931

  You may need to look at sp_executesql

  See SQL Server Books Online for 'sp_executesql'

• 5409045121009-7368

  SSCoach

  Points: 15740

  More actions

  April 2, 2003 at 6:32 pm

  #452938

  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

  April 2, 2003 at 9:23 pm

  #452943

  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

  
  Jay Madren

• bruce_reid

  SSCrazy

  Points: 2419

  More actions

  April 4, 2003 at 1:42 pm

  #453245

  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...