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
