| Author |
Topic |
|
aggiekevin
Starting Member
14 Posts |
 Posted - 2007-08-14 : 18:09:24
|
| I've got a database which contains more than records. Returning all the records takes too long, so I'd like to be able to query a subset of 100 records at a time (records 0-99, 100-199, etc). How would I do this? Thanks! |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-14 : 22:02:06
|
| Does the table have any id column? |
 |
|
|
aggiekevin
Starting Member
14 Posts |
Posted - 2007-08-14 : 23:53:23
|
quote:
Originally posted by rmiao
Does the table have any id column?
Yes the table has an id column. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-15 : 00:08:39
|
| Then you can select rows based on id. |
|
|
|
aggiekevin
Starting Member
14 Posts |
Posted - 2007-08-15 : 10:29:49
|
quote:
Originally posted by rmiao
Then you can select rows based on id.
Yes but then I would always get a different number of records returned from the query (because sometimes a record is deleted). |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-08-15 : 10:32:57
|
[code]SELECT *FROM( SELECT *, row = row_number() OVER (ORDER BY id) FROM yourtable) aWHERE row BETWEEN 100 AND 199[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
aggiekevin
Starting Member
14 Posts |
Posted - 2007-08-15 : 10:40:43
|
| Actually I was thinking and your idea seems like a good one. If I keep track of the id of the last record retrieved and pass it into the stored proc I could do something like:SELECT TOP 100 [columnname]FROM [table] tWHERE t.id > @last_record_idI did some testing and this accomplishes exactly what I wanted. Thanks! |
|
|
|
aggiekevin
Starting Member
14 Posts |
Posted - 2007-08-15 : 10:46:14
|
quote:
Originally posted by khtan
SELECT *FROM( SELECT *, row = row_number() OVER (ORDER BY id) FROM yourtable) aWHERE row BETWEEN 100 AND 199
KH[spoiler]Time is always against us[/spoiler]
Yeah that would be a nice solution, however I am using SQL Server 2000, so I cannot use the row_number() function. I'm sorry I didn't specify this in my original post. Thanks for the response though. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-08-15 : 10:48:33
|
You should have posted over in SQL 2000 forum not 2005.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
aggiekevin
Starting Member
14 Posts |
Posted - 2007-08-15 : 10:54:43
|
| Yeah I apologize. I'm new to these forums and didn't realize there were different forums for the 2000 and 2005 versions. Thanks for your response though! |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
select a subset of records