Re: Determining offsets to jump to grab a group of records

David Wilson wrote:
> This isn't an answer to your direct question, but it seems to me as if
> you've already decided on a solution (figuring out an offset) that's
> non-optimal. If you're using offsets at all, you must have a distinct
> sort ordering; if you have that, you should be able to accomplish the
> same thing with a where clause instead of an offset. Is there a
> particular reason why this doesn't work for you?
>
> Perhaps the better question is "what are you trying to do with this?"
> There may be a higher level solution that can be given.

We are converting from an old FoxPro (record based) system to using SQL.
We would like to give the users the illusion that they are still
record based but using a SQL backend. To do this we would use LIMIT and
OFFSET to allow them to go row by row through the SELECT statement.
This would work fine in a paging usage but what if we want to position
the grid to where the names start with "DAVID". In FoxPro you would
have an index on name and then just seek on "DAVID". In an attempt to
reproduce this behavior we wanted to figure out what the starting offset
was in the SELECT statement so that we could load the grid with 25+
records around it and allow the user to go up and down through the rows.

We have already looked into using CURSORS but they must be within a
transaction and we could have many of these grids open at any given time
looking at different tables.

So the end result is that we are trying to give users the freedom to go
through their data in a grid like fashion with seeking and positioning.

We have used direct WHERE clauses in our asp.net applications but we
wanted the desktop application to be a little bit more responsive and
easy to use.

Is there a better way to approach this?

David Lambert