pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow do you select from a table until a condition is met?
11+ messages / 3 participants
[nested] [flat]
* How do you select from a table until a condition is met?
@ 2003-02-12 18:10 Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 20:39 ` Re: How do you select from a table until a condition is met? Dmitry Tkach <dmitry@openratings.com>
0 siblings, 1 reply; 11+ messages in thread
From: Nicholas Allen @ 2003-02-12 18:10 UTC (permalink / raw)
To: pgsql-sql
Hi,
I am hoping that someone can help me with a problem I have. I would like to be
able to perform a SELECT query to select values from a table upto a certain
row. The select may be ordered on a number of items. Is it possible to do
this and how should I do it?
The reason I need this is because I have created a virtual table browser class
that performs SQL queries to get the sections of the table the user is
interested in. This way I don't load the whole table onto the client side. If
the user changes the sort ordering I want to determine the row index that was
previously selected and scroll to that location.
Any help would be very much appreciated.
Thanks in advance,
Nicholas Allen
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How do you select from a table until a condition is met?
2003-02-12 18:10 How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
@ 2003-02-12 20:39 ` Dmitry Tkach <dmitry@openratings.com>
2003-02-12 19:10 ` Re: How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
0 siblings, 1 reply; 11+ messages in thread
From: Dmitry Tkach @ 2003-02-12 20:39 UTC (permalink / raw)
To: Nicholas Allen <nallen@freenet.co.uk>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How do you select from a table until a condition is met?
2003-02-12 18:10 How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 20:39 ` Re: How do you select from a table until a condition is met? Dmitry Tkach <dmitry@openratings.com>
@ 2003-02-12 19:10 ` Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 19:35 ` Re: How do you select from a table until a condition is met? Bruno Wolff III <bruno@wolff.to>
2003-02-12 20:59 ` Re: How do you select from a table until a condition is met? Dmitry Tkach <dmitry@openratings.com>
0 siblings, 2 replies; 11+ messages in thread
From: Nicholas Allen @ 2003-02-12 19:10 UTC (permalink / raw)
To: Dmitry Tkach <dmitry@openratings.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How do you select from a table until a condition is met?
2003-02-12 18:10 How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 20:39 ` Re: How do you select from a table until a condition is met? Dmitry Tkach <dmitry@openratings.com>
2003-02-12 19:10 ` Re: How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
@ 2003-02-12 19:35 ` Bruno Wolff III <bruno@wolff.to>
2003-02-12 19:55 ` Re: How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
1 sibling, 1 reply; 11+ messages in thread
From: Bruno Wolff III @ 2003-02-12 19:35 UTC (permalink / raw)
To: Nicholas Allen <nallen@freenet.co.uk>; +Cc: Dmitry Tkach <dmitry@openratings.com>; pgsql-sql
On Wed, Feb 12, 2003 at 20:10:15 +0100,
Nicholas Allen <nallen@freenet.co.uk> 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....
Since you are ordering the data you can use where clauses with < (and/or >)
operators to stop counting at the desired row. For this to work the ordering
needs to be well defined. You can tack on the primary key to your list
of columns to order by to make the ordering well defined if it isn't
already.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How do you select from a table until a condition is met?
2003-02-12 18:10 How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 20:39 ` Re: How do you select from a table until a condition is met? Dmitry Tkach <dmitry@openratings.com>
2003-02-12 19:10 ` Re: How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 19:35 ` Re: How do you select from a table until a condition is met? Bruno Wolff III <bruno@wolff.to>
@ 2003-02-12 19:55 ` Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 20:37 ` Re: How do you select from a table until a condition is met? Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 11+ messages in thread
From: Nicholas Allen @ 2003-02-12 19:55 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Dmitry Tkach <dmitry@openratings.com>; pgsql-sql
I thought of this but the problem is that there may be multiple rows with the
same value for the column I am sorting on. Eg if sorting on a surname then
there may be 100s of people with the same surname so generating a where
clause that selects up to the exact person previously selected is very
difficult.
eg where surname <= 'Jones' could return hundreds of records and probably some
records for people with the same surname but which appear after the selected
record.
I had another idea though. Do you know if it is possible to write a function
for postgresql thatwill return true until a condition is met and then for
every row after that return false? This way I could write a function that
could select up to a particular row using the where clause and calling the
function from the where claus.
I'm sure there must be a way of doing this as it is such an obvious thing to
need to do...
Thanks for the help!
On Wednesday 12 Feb 2003 8:35 pm, Bruno Wolff III wrote:
> On Wed, Feb 12, 2003 at 20:10:15 +0100,
>
> Nicholas Allen <nallen@freenet.co.uk> 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....
>
> Since you are ordering the data you can use where clauses with < (and/or >)
> operators to stop counting at the desired row. For this to work the
> ordering needs to be well defined. You can tack on the primary key to your
> list of columns to order by to make the ordering well defined if it isn't
> already.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How do you select from a table until a condition is met?
2003-02-12 18:10 How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 20:39 ` Re: How do you select from a table until a condition is met? Dmitry Tkach <dmitry@openratings.com>
2003-02-12 19:10 ` Re: How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 19:35 ` Re: How do you select from a table until a condition is met? Bruno Wolff III <bruno@wolff.to>
2003-02-12 19:55 ` Re: How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
@ 2003-02-12 20:37 ` Bruno Wolff III <bruno@wolff.to>
2003-02-12 20:53 ` Re: How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
0 siblings, 1 reply; 11+ messages in thread
From: Bruno Wolff III @ 2003-02-12 20:37 UTC (permalink / raw)
To: Nicholas Allen <nallen@freenet.co.uk>; +Cc: Dmitry Tkach <dmitry@openratings.com>; pgsql-sql
On Wed, Feb 12, 2003 at 20:55:21 +0100,
Nicholas Allen <nallen@freenet.co.uk> wrote:
> I thought of this but the problem is that there may be multiple rows with the
> same value for the column I am sorting on. Eg if sorting on a surname then
> there may be 100s of people with the same surname so generating a where
> clause that selects up to the exact person previously selected is very
> difficult.
Then you should sort on surname AND whatever you are using as the primary key.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How do you select from a table until a condition is met?
2003-02-12 18:10 How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 20:39 ` Re: How do you select from a table until a condition is met? Dmitry Tkach <dmitry@openratings.com>
2003-02-12 19:10 ` Re: How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 19:35 ` Re: How do you select from a table until a condition is met? Bruno Wolff III <bruno@wolff.to>
2003-02-12 19:55 ` Re: How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 20:37 ` Re: How do you select from a table until a condition is met? Bruno Wolff III <bruno@wolff.to>
@ 2003-02-12 20:53 ` Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 22:45 ` Re: How do you select from a table until a condition is met? Dmitry Tkach <dmitry@openratings.com>
0 siblings, 1 reply; 11+ messages in thread
From: Nicholas Allen @ 2003-02-12 20:53 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Dmitry Tkach <dmitry@openratings.com>; pgsql-sql
Ok I thought of that but what happens if there is no primary key in the table?
I can probably add primary keys to the table but I didn't design the tables
and so I have little (but luckily some) say over what columns appear in them.
What has actually happened is that we have a view on a table and the view
doesn't return the primary key. I'll try and ask the database administrator
to add the primary keys.
Thanks for the help though I guess it is the only way to do it. I was just
hoping there would be a way to do it without a promary key to prevent changes
to our database views.
On Wednesday 12 Feb 2003 9:37 pm, Bruno Wolff III wrote:
> On Wed, Feb 12, 2003 at 20:55:21 +0100,
>
> Nicholas Allen <nallen@freenet.co.uk> wrote:
> > I thought of this but the problem is that there may be multiple rows with
> > the same value for the column I am sorting on. Eg if sorting on a surname
> > then there may be 100s of people with the same surname so generating a
> > where clause that selects up to the exact person previously selected is
> > very difficult.
>
> Then you should sort on surname AND whatever you are using as the primary
> key.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How do you select from a table until a condition is met?
2003-02-12 18:10 How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 20:39 ` Re: How do you select from a table until a condition is met? Dmitry Tkach <dmitry@openratings.com>
2003-02-12 19:10 ` Re: How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 19:35 ` Re: How do you select from a table until a condition is met? Bruno Wolff III <bruno@wolff.to>
2003-02-12 19:55 ` Re: How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 20:37 ` Re: How do you select from a table until a condition is met? Bruno Wolff III <bruno@wolff.to>
2003-02-12 20:53 ` Re: How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
@ 2003-02-12 22:45 ` Dmitry Tkach <dmitry@openratings.com>
0 siblings, 0 replies; 11+ messages in thread
From: Dmitry Tkach @ 2003-02-12 22:45 UTC (permalink / raw)
To: Nicholas Allen <nallen@freenet.co.uk>; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-sql
Nicholas Allen wrote:
>Ok I thought of that but what happens if there is no primary key in the table?
>
Then tough luck - as you said yourself, since everything else can be
duplicated, the primary key is the only way to
tell for sure which exact row you are talking about.
>I can probably add primary keys to the table but I didn't design the tables
>and so I have little (but luckily some) say over what columns appear in them.
>What has actually happened is that we have a view on a table and the view
>doesn't return the primary key. I'll try and ask the database administrator
>to add the primary keys.
>
>Thanks for the help though I guess it is the only way to do it. I was just
>hoping there would be a way to do it without a promary key to prevent changes
>to our database views.
>
I think you are better off redesigning your interface - even if you do
modify the view, and use those suggestions I mentioned earlier,
it will be slow like hell, unless your table only contains a few hundred
rows, in which case it doesn't matter if you send the whole table to the
client every time,
Dima
>
>On Wednesday 12 Feb 2003 9:37 pm, Bruno Wolff III wrote:
>
>
>>On Wed, Feb 12, 2003 at 20:55:21 +0100,
>>
>> Nicholas Allen <nallen@freenet.co.uk> wrote:
>>
>>
>>>I thought of this but the problem is that there may be multiple rows with
>>>the same value for the column I am sorting on. Eg if sorting on a surname
>>>then there may be 100s of people with the same surname so generating a
>>>where clause that selects up to the exact person previously selected is
>>>very difficult.
>>>
>>>
>>Then you should sort on surname AND whatever you are using as the primary
>>key.
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.or
>>
>g
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How do you select from a table until a condition is met?
2003-02-12 18:10 How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 20:39 ` Re: How do you select from a table until a condition is met? Dmitry Tkach <dmitry@openratings.com>
2003-02-12 19:10 ` Re: How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
@ 2003-02-12 20:59 ` Dmitry Tkach <dmitry@openratings.com>
2003-02-12 20:00 ` Re: How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
1 sibling, 1 reply; 11+ messages in thread
From: Dmitry Tkach @ 2003-02-12 20:59 UTC (permalink / raw)
To: Nicholas Allen <nallen@freenet.co.uk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How do you select from a table until a condition is met?
2003-02-12 18:10 How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 20:39 ` Re: How do you select from a table until a condition is met? Dmitry Tkach <dmitry@openratings.com>
2003-02-12 19:10 ` Re: How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 20:59 ` Re: How do you select from a table until a condition is met? Dmitry Tkach <dmitry@openratings.com>
@ 2003-02-12 20:00 ` Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 22:04 ` Re: How do you select from a table until a condition is met? Dmitry Tkach <dmitry@openratings.com>
0 siblings, 1 reply; 11+ messages in thread
From: Nicholas Allen @ 2003-02-12 20:00 UTC (permalink / raw)
To: Dmitry Tkach <dmitry@openratings.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How do you select from a table until a condition is met?
2003-02-12 18:10 How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 20:39 ` Re: How do you select from a table until a condition is met? Dmitry Tkach <dmitry@openratings.com>
2003-02-12 19:10 ` Re: How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 20:59 ` Re: How do you select from a table until a condition is met? Dmitry Tkach <dmitry@openratings.com>
2003-02-12 20:00 ` Re: How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
@ 2003-02-12 22:04 ` Dmitry Tkach <dmitry@openratings.com>
0 siblings, 0 replies; 11+ messages in thread
From: Dmitry Tkach @ 2003-02-12 22:04 UTC (permalink / raw)
To: Nicholas Allen <nallen@freenet.co.uk>; +Cc: pgsql-sql
>
>
>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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2003-02-12 22:45 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-12 18:10 How do you select from a table until a condition is met? Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 20:39 ` Dmitry Tkach <dmitry@openratings.com>
2003-02-12 19:10 ` Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 19:35 ` Bruno Wolff III <bruno@wolff.to>
2003-02-12 19:55 ` Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 20:37 ` Bruno Wolff III <bruno@wolff.to>
2003-02-12 20:53 ` Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 22:45 ` Dmitry Tkach <dmitry@openratings.com>
2003-02-12 20:59 ` Dmitry Tkach <dmitry@openratings.com>
2003-02-12 20:00 ` Nicholas Allen <nallen@freenet.co.uk>
2003-02-12 22:04 ` Dmitry Tkach <dmitry@openratings.com>
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