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 21:00:11 +0100
Message-ID: <200302122100.11398.nallen@freenet.co.uk> (raw)
In-Reply-To: <3E4AB5C4.4040105@openratings.com>
References: <200302121910.38232.nallen@freenet.co.uk>
<200302122010.15297.nallen@freenet.co.uk>
<3E4AB5C4.4040105@openratings.com>
On Wednesday 12 Feb 2003 9:59 pm, Dmitry Tkach wrote:
> Nicholas Allen wrote:
> >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?
>
> I still don't understand what it is you are trying to do...
>
> Perhaps, an example would help... Suppose you have a table like this:
>
> create table people
> (
> id serial primary key,
> first_name text,
> last_name text
> );
>
>
> Now, suppose, the user gets the list of people, ordered by the
> last_name, scrolls through, say, 100 of them, and then switches the
> order to sort by the first_name...
> Are you saying that in this case you want to start with the 101st entry
> in the first name order?
No. What I'm saying is the user scrolls through a list then selects an item.
They then decide to sort on something else but they still want the item they
selected to be selected after the sort has completed. So I need some way to
work out where the previously selected item will be in the new sort order so
I can scroll to that position and select it again.
eg If user selects "John Doe" when sorted by First name and then decides to
sort by Surname I still want John Doe selected and the user should be able to
see the record.
> It makes little sense to me (the entries you are going to skip have
> nothing to do with the ones the user already saw - so, I can't imagine
> the reason not to just start
> with the first entry, using the new sort order)...
> And you can still do it with the offsets:
>
> select * from people order by last_name limit 10;
> ...
> select * from people order by last_name offset 90 limit 10;
> ...
> select * from people order by first_name offset 100 limit 10;
>
> This will still work... But I doubt that's what you want... I must be
> missing something...
>
> Dima
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
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: <200302122100.11398.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