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

I am trying to find a way to figure out what offset I would have to use
in a SELECT with LIMIT and OFFSET clauses to get a grouping of records.
For example:

   Consider a table full of first names.  I want to be able to find the
first offset where the name is "DAVID".  (We'll say that it is the
1009th row in the resulting select) This way I could perform the following:

    SELECT name FROM nametable LIMIT 25 OFFSET 1009;

Is this possible with PostgresQL?  I have already tried the following
using a temporary sequence.

    CREATE TEMP SEQUENCE RNUM;
    SELECT newoffset FROM (SELECT nextval('RNUM') AS newoffset, X.* FROM
(SELECT name FROM nametable ORDER BY name) X) Y WHERE name='DAVID';


Unfortunately, this just returns a bunch of rows with "1,2,3,4,5"
instead of "1009,1010,1011".  It seems that the nextval('RNUM') is only
executed once the outer select is being evaluated.  Is there a way
around this?

If I execute just the inner select:

    SELECT nextval('RNUM') AS newoffset, X.* FROM (SELECT name FROM
nametable ORDER BY name) X

Then it outputs the correct numbers but doesn't filter out the records
that I need.

Does anyone know of a different way to calculate an approximate offset?
  Any help you can provide is greatly appreciated.

David Lambert

On Wed, Jun 11, 2008 at 5:54 PM, David Lambert <davidl@dmisoft.com> wrote:
> I am trying to find a way to figure out what offset I would have to use in a
> SELECT with LIMIT and OFFSET clauses to get a grouping of records. For
> example:
>
>  Consider a table full of first names.  I want to be able to find the first
> offset where the name is "DAVID".  (We'll say that it is the 1009th row in
> the resulting select) This way I could perform the following:
>
>        SELECT name FROM nametable LIMIT 25 OFFSET 1009;
>

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.


--
- David T. Wilson
david.t.wilson@gmail.com

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

On Wed, Jun 11, 2008 at 4:39 PM, David Lambert <davidl@dmisoft.com> wrote:
>
> 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?

Yes there is.  Use an indexed id field of some kind.

select * from table where idfield between 0 and 100;
select * from table where idfield between 1000000 and 1000100;

Will both be equally fast.  Offset / limit syntax requires the db to
materialize <offset>+<limit> rows for the query.  between and an id
does not.

Scott Marlowe wrote:
> Yes there is.  Use an indexed id field of some kind.
>
> select * from table where idfield between 0 and 100;
> select * from table where idfield between 1000000 and 1000100;
>
> Will both be equally fast.  Offset / limit syntax requires the db to
> materialize <offset>+<limit> rows for the query.  between and an id
> does not.

An ID would work fine if you could always guarantee that you were
ordering by the id.  If not then the ID fields would be in non
sequential order which would throw off the between above and give you
incorrect data.

We need something that would be independent from the ordering that way a
user could order it by first name, last name, brother's wife's maiden
name, etc. and it would still work correctly.

Are there any other ways to accomplish this?  Thanks.

David Lambert