From: Dmitry Tkach <dmitry@openratings.com>
To: Nicholas Allen <nallen@freenet.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: How do you select from a table until a condition is met?
Date: Wed, 12 Feb 2003 15:59:48 -0500
Message-ID: <3E4AB5C4.4040105@openratings.com> (raw)
In-Reply-To: <200302122010.15297.nallen@freenet.co.uk>
References: <200302121910.38232.nallen@freenet.co.uk>
<3E4AB0E7.3010101@openratings.com>
<200302122010.15297.nallen@freenet.co.uk>
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?
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
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: dmitry@openratings.com, nallen@freenet.co.uk
Subject: Re: How do you select from a table until a condition is met?
In-Reply-To: <3E4AB5C4.4040105@openratings.com>
* 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