From: Ragnar Hafstað <gnari@simnet.is>
To: Alain <alainm@pobox.com>
Cc: Sql-Postgre <pgsql-sql@postgresql.org>
Subject: Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
Date: Wed, 18 May 2005 08:28:25 +0000
Message-ID: <1116404906.9362.19.camel@localhost.localdomain> (raw)
In-Reply-To: <428AA569.3010708@pobox.com>
References: <DCD4903594CCF34CAFE200BDF3448D8D5B291A@tshuscodenmbx01.ERF.THOMSON.COM>
<20050517170636.GC12405@phlogiston.dyndns.org>
<428A2E81.2060501@pobox.com>
<1116353539.26520.5.camel@localhost.localdomain>
<428A3B54.4070902@pobox.com>
<1116367382.9362.11.camel@localhost.localdomain>
<428AA569.3010708@pobox.com>
On Tue, 2005-05-17 at 23:16 -0300, Alain wrote:
>
> Ragnar Hafstað escreveu:
> >>[how to solve the get next 100 records problem]
> BUT, I think that this is close to a final solution, I made some
> preliminary test ok. Please tell me what you think about this.
>
> Fisrt let's state that I am reading records to put on a screen (in a
> Table/Grid). I separated the problem is *3* parts
>
> -first select is as above:
> select ... from tab ORDER by skey,pkey LIMIT 100;
>
> -second method for next 100:
> select ... from tab WHERE skey>=skey_last
> ORDER BY skey,pkey
> LIMIT 100;
> but here I test for repetitions using pkey and discard them
>
> -now if I get all repetitions or the last 100 have the same skey with
> the second method, I use
> select ... from tab WHERE skey=skey_last AND pkey>pkey_last
> ORDER BY skey,pkey
> LIMIT 100;
> until I get an empty response, then I go back to the second method.
if your distribution is such that those skeys that have > 100 records
tend to have a lot more, you might have a higher limit for this case.
> All queries are extremely fast with 6000000 records and it looks like
> the few redundant or empty queries (but very fast) will not be a problem.
>
> What is your opinion about this (apart that it is a bit complex :) ??
looks fine
gnari
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gnari@simnet.is, alainm@pobox.com
Subject: Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
In-Reply-To: <1116404906.9362.19.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox