Select rows n to nn....

[m0nkey]

Hi all,
Is there a way to select a certain range of results? I have a large database (75 million rows) and when I get a result set it is usually about 200k rows...if I wanted to select the top 50K that would be just a top 50k statement but what if I wanted row 50K through 100K....i was hoping to save this row number range in some variable and then save all the search parameter variables with it from the same sproc to a table so i can come back the next day and generate a result set with the next 50K rows till the sproc is done pulling on those variables passed it....

is this at all clear...i can explain further...

thanks as always...

mark

 

[tlbroadbent]

The table should have a primary key. You could select 50K rows starting with the MIN PK value, ordering by the PK ascending. You could save the last primary key processed and select the next 50K rows where the PK is greater than the stored value.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[m0nkey]

I also had this in mind ,but yours seems better...thanks....


select *, identity(38) as "rownum" into #foobar from mytable where [...]
order by [...]

select * from #foobar where rownum between 15 and 22

drop table #foobar go

mark