Top 2 records for the first time and so on in SQL Server 2000

Question

I have a table called Test:

ID  |   Name    
------------
1   |   ABC  
2   |   XYZ  
3   |   LMN  
4   |   JHF 

I want to select top 2 rows which I can do it easily

select top(2) * from test.

But what I want to achieve is when I run the same query again it will show me the next 2 records.

Every time I run the same query it will give me the next 2 results.

I am working with SQL Server 2000, so I cannot use row_number() and offset and fetch.

So is there any way we can use it in SQL Server 2000?

Answer 1

You might require to simulate Row_Number() using subquery as below:

Select (Select count(*) from #Names n
        where n.[name] <= oq.[name] ) as RowN, Id, [Name]
    from #names oq

And pass appropriate two numbers on every pass from client

With this above query you can generate/simulate Row_Number based on Name as below:

+------+----+------+
| RowN | Id | Name |
+------+----+------+
|    1 |  1 | ABC  |
|    2 |  4 | JHF  |
|    3 |  3 | LMN  |
|    4 |  2 | XYZ  |
+------+----+------+

On each client pass send start number and end number from client and apply in where condition in RowN.

Demo