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 17:04:17 -0500
Message-ID: <3E4AC4E1.3040700@openratings.com> (raw)
In-Reply-To: <200302122100.11398.nallen@freenet.co.uk>
References: <200302121910.38232.nallen@freenet.co.uk>
<200302122010.15297.nallen@freenet.co.uk>
<3E4AB5C4.4040105@openratings.com>
<200302122100.11398.nallen@freenet.co.uk>
>
>
>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.
>
>
What do you mean by 'select the item'? Get all the rows before it?
This still doesn't make much sense to me - you said before, that the
whole purpose of your 'browser' was to avoid sending tons of data to the
client side, but if I understand correctly what you are thinking about
doing, it won't work, because you do not know how many rows are before
the 'selected' item. Am I still missing something?
Anyway, what about this:
select * from people order by last_name, id limit 100; -- get first 100,
ordered by the last_name
select * from people where first_name < 'John' or first_name = 'John'
and last_name <= 'Doe' and id <= "JohnDoe's ID" order by first_name, id;
-- get everything before the last seen John Doe ordered by the first name
select * from people where first_name > 'John' or first_name = 'John'
and last_name >= 'Doe' and id >= "JohDoe's ID" order by first_name, id
limit 100;
-- get 100 rows after the selected one sorted by first name
etc...
Still can't imagine how this can be useful though :-(
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: <3E4AC4E1.3040700@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