Limiting the number of records returned by a SQL query

[Boblevien]

I'm trying to carry out some searches of what will be a very large database and some of the searches could return huge recordsets. I want to display the results in pages of 10 or so records. Rather than download a huge recordset and then step through in ten records at a time I would like my SQL to take the strain.

The only way I have found is to use something like:
objRS.MaxRecords 10
but it never works for me. Is there anyway of building it into the SQL query string?

Bob..

 

[Telsa]

Do you have a sequential numbering field? Then you can say something like

select * from table where id between 1 and 800

Figuring 80 lines per page... that is.

Mary

 

[Boblevien]

Hi Mary,

Thanks for that but that's the problem - since I can't predict the results of the search I don't have that option. What I'm looking for is something like "WHERE count of id = 80"

Any ideas?

Bob..

 

[Shep]

To limit the records coming back from a select you can use TOP:

Select TOP 10 * from tblTableName

This will give you the first 10 records matching your criteria - not sure how you'd go about getting the next 10 though..

Shep

 

[Boblevien]

Hi

Thanks - that may well be it. Once I'd got the top ten I'd know the id of the tenth item and the next query could start from there (probably!)

Thanks.

Bob..