Re: [GENERAL] Determining offsets to jump to grab a group of records
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 100 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Determining offsets to jump to grab a group of records
On Wed, Jun 11, 2008 at 5:54 PM, David Lambert [EMAIL PROTECTED] 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 [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Determining offsets to jump to grab a group of records
On Wed, Jun 11, 2008 at 4:39 PM, David Lambert [EMAIL PROTECTED] 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 100 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
