SELECT the next 3

[gwendaal]

How can I get the next 3 rows from a query ?

1 to 3
then
4 to 7
ect ....

something like
SELECT TOP (4 to 7) id FROM myTable ORDER BY id DESC

will be great :-)

thank you

[tdambra]

An SQL guru may have a different take on this, but I would use a DAO recordset in code that filters out each iteration of a TOP 3 query on the previous result...

[Rawhide]

You have two choices:

1. Use pagination where you specify a page size of 3 and keep fetching the next page.

2. Use a field with unique entries (like an autonumber field) that you can use for a reference.

Select Top 3 *
From myTable
Order By keyField
Where keyField > "largest keyField value returned in last query"

[gwendaal]

pagination is nearlly never the best way...
im my case most part of customers will never turn the next page
... it is unnecessary to load 1000 rows for 10 percent of customers

--------------------

Rawhide >>> of course I need the second choise


Select Top 3 *
From myTable
Order By id
Where id > "largest id value returned in last query"

is just ..... great :-))))


thank you

[Rawhide]

I must admit that I never use pagination myself, but still i felt compelled to mention it.

[gwendaal]

it works allready and that's really a great idea ....
I use a generally a normal datapaging component ....

[gwendaal]

it works very well to get datas but if you need to sort by columns it doesn't work any more

is there any way to get the row index not depending on values ?

Select Top 10 myTable.*
From myTable
Where myTable.RowIndex > lastRowIndex

of course RowIndex is not a column Name

thank you