From: Nicholas Allen <nallen@freenet.co.uk>
To: Dmitry Tkach <dmitry@openratings.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: How do you select from a table until a condition is met?
Date: Wed, 12 Feb 2003 20:10:15 +0100
Message-ID: <200302122010.15297.nallen@freenet.co.uk> (raw)
In-Reply-To: <3E4AB0E7.3010101@openratings.com>
References: <200302121910.38232.nallen@freenet.co.uk>
<3E4AB0E7.3010101@openratings.com>
Thanks, but I don't think thats what I want. Basically I need to get the row
offset of a record (ie row) in a result set returned from the database. I
know all the details of the row in question but when the search order changes
I need to know the new index the row will have.
In other words, I was hoping I would be able to select upto a particular row
(not knowing its offset but knowing everything else about it) in a select
query.
Ideally I could do something like SELECT count(*) UNTIL col1=value1 &&
col2=val2 etc....
Of course there is no UNTIL in a select statement just a WHERE clause.
I know I didn't explain that very well!
Well I hope you can still help?
On Wednesday 12 Feb 2003 9:39 pm, Dmitry Tkach wrote:
> select * from table order by x limit 10; -- get first 10
> select * from table order by x offset 10 limit 10; -- get 10 - 20
> select * from table order by x offset 40 limit 10; -- get 40-50
>
> OR
>
> begin;
> declare mycursor cursor for select * from table order by x;
> fetch 10 from mycursor; -- get first 10
> fetch 10 from mycursor; -- get 10-20
> move 20 in mycursor;
> fetch 10 from mycursor; -- get 40-50
>
>
>
> Is that what you are talking about?
>
> Dima
>
> Nicholas Allen wrote:
> > Hi,
> >
> > I am hoping that someone can help me with a problem I have. I would like
> > to= be=20
> > able to perform a SELECT query to select values from a table upto a
> > certain= =20
> > row. The select may be ordered on a number of items. Is it possible to
> > do= =20
> > this and how should I do it?
> >
> > The reason I need this is because I have created a virtual table browser
> > cl= ass=20
> > that performs SQL queries to get the sections of the table the user is=20
> > interested in. This way I don't load the whole table onto the client
> > side. = If=20
> > the user changes the sort ordering I want to determine the row index that
> > w= as=20
> > previously selected and scroll to that location.
> >
> > Any help would be very much appreciated.
> >
> > Thanks in advance,
> >
> > Nicholas Allen
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
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: nallen@freenet.co.uk, dmitry@openratings.com
Subject: Re: How do you select from a table until a condition is met?
In-Reply-To: <200302122010.15297.nallen@freenet.co.uk>
* 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