Number Records or Return Middle N Records 1

[MajP]

I know that I can select the Top or bottom N records, but is there a way to select for example records 10 through 20? Also is there is a way to build a calculated field that numbers the records. This would also be a solution.
I have to do this because of some work I am doing with sorting nodes in a Treeview. The ineloquent way I do it now is to have a node sort order field in my table. I create a recordset of my query, read each record, and then sequentially number the record in the table. I then open the query with each record sequentially numbered in my node sort order field. I can then grab records 10 through 20. This works fine, but I like to avoid looping through records whenever possible.

Thanks

 

[earthandfire]

Table3 has one field f1

Try something like:

SELECT top 11 Table3.f1
FROM Table3
WHERE (((Table3.f1) Not In (select top 9 f1 from table3)));

The sub-query selects the top 9 records, the main query selects the top 11 records that are not in the top 9 ie records 10 to 20


Hope this helps.

 

[r937]

TOP makes no sense whatsoever without ORDER BY

r937.com | rudy.ca

 

[PCLewis]

My concern is that according to something I remember reading a long time ago, nothing guarantees that the top n records will be the same top n as next time unless you use a sort criteria. Access seems to return the same top n every time when no search sort is specified (It seems to return rows in the order that they were inserted but that's not promised as far as I know)

So I think that when using the solution above, you should include an order by clause as well.

To solve your problem at cause the least table scan's, I'd just have another column representing the "table position". I'm not certain for the need to get only 10 records at a time but sinc eyou have such a need, I believe that having a number field and adding a clause to your criteria to select only the records you want is going to be faster than the more generic colution.

 

[PCLewis]

r937 beat me to it and was rather more succinct

 

[MajP]

Very eloquent. Thanks.

 

[MajP]

I knew what you meant.

 

[earthandfire]

The purpose of my snippet was to demonstrate selecting the top x records other than the first x and was not intended as a definitive solution hence the prefix "try something like"

Hope this helps.