SQL limit query

I'm having an issue with limiting the SQL query. I'm using SQL 2000 so I can't use any of the functions like ROW_NUMBER(),CTE OR OFFSET_ROW FETCH.

I have tried the Select TOP limit * FROM approach and excluded the already shown results but this way the query is so slow because sometimes my result query fetches more than 10000 records.

Also I have tried the following approach:

SELECT * FROM (
 SELECT DISTINCT TOP 100 PERCENT i.name, i.location, i.image ,  
 ( SELECT count(DISTINCT i.id) FROM image WHERE i.id<= im.id ) AS recordnum 
 FROM images AS im 
 order by im.location asc, im.name asc) as tmp 
 WHERE recordnum between 5 AND 15

same problem here plus issue because I couldn't add ORDER option in sub query from record um. I have placed both solution in stored procedure but still the query execution is still so slow.

So my question is: IS there an efficient way to limit the query to pull 20 records per page in SQL 2000 for large amounts of data i.e more than 10000?

Thanks.

Solution

Now the subquery is only run once
where im2.id is null will skip the first 40 rows

SELECT top 25 im1.* 
FROM images im1 
left join ( select top 40 id from images order by id ) im2
on im1.id = im2.id
where im2.id is null
order by im1.id