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
