In Access97 SQL how do I return a range of rows?

Question

Hi, I have an Access97 database (unfortunately) and I am querying it over ODBC.

I want to construct a query that will return me J ordered rows starting at offset K. Right now for a related operation I am using a sub-query and the TOP keyword, this works fine but I don't think I can use it for this new operation I am trying to perform. I am wanting to do this because I want to add some form of paging to some software.

Ideally the user interface should be able to say, give me 50 records, starting at record 150, sorted by the date column.

Any help would be appreciated.

Answer 1

You're basically trying to mimic ROW_NUMBER() in Access. Unfortunately, Access is not a good engine for doing this kind of thing. It gets dog slow after a hundred or so records.

With that said, here's what you can do:

SELECT t1.ID, t1.SomeText, t1.SomeDate,
(
  SELECT COUNT(*) + 1
  FROM Table1 AS t2
  WHERE t1.SomeText > t2.SomeText
) as RowNumber
FROM Table1 t1
ORDER BY t1.SomeText

That will produce this resultset (in my particular table):

ID | SomeText             | SomeDate  | RowNumber
10 | 4NGJN                | 1/20/2010 | 1
11 | ABH                  | 1/20/2010 | 2
2  | asldkfj              | 1/20/2010 | 3
1  | asldkfjaslgh         | 1/20/2010 | 4
7  | ewoiuhdkjnlbkjbn     | 1/20/2010 | 5
4  | oihkjldhnlkmdfn,mn   | 1/20/2010 | 6
3  | oihoih               | 1/20/2010 | 7
5  | qwwern               | 1/20/2010 | 8
8  | SKN                  | 1/20/2010 | 9
9  | WEOIN                | 1/20/2010 | 10
6  | wetrhn               | 1/20/2010 | 11
12 | XDNKJ                | 1/20/2010 | 12

I would make a saved query in Access (a view in a SQL Server) and then write a query like this in your application:

SELECT ID, SomeText, SomeDate, RowNumber
FROM myView
WHERE RowNumber > 5
  AND RowNumber < 11

Of course, "5" & "11" you would pass in from your client app, and would be based on the particular page you're on. (It would probably be 50 and 100 or something for you).

Answer 2

You can use TOP with Not In and a TOP subquery:

 SELECT TOP n ... ID Not In (SELECT TOP k ...) 

It will probably be faster than trying to number rows in Access