Re: How do you select from a table until a condition is met?

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