From: | Lincoln Yeoh <lylyeoh(at)mecomb(dot)com> |
---|---|
To: | Eugene Karpachov <jk(at)steel(dot)orel(dot)ru> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to select a row and an adjacent row? |
Date: | 2000-06-23 08:03:03 |
Message-ID: | 3.0.5.32.20000623160303.008514b0@pop.mecomb.po.my |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
At 10:55 AM 23-06-2000 +0400, you wrote:
>Fri, Jun 23, 2000 at 02:21:17PM +0800, Lincoln Yeoh:
>> Just say I'm doing a select ... from ... where ... order by...
>> I know the row I want, but I also want the row after (or before in another
>> case), and the row I want is NOT at the top/bottom of the order by :(.
>
>You could probably do this:
>
>select * from yourtable where <key> > <known_row_key>
> order by <key> limit 1;
>
>- isn't it?
Thanks. Looks like that would work. Dunno why previously I thought that
wouldn't have worked- fuzzyminded today I guess.
More info: in my case I have a list of stuff - say the fields are:
messageid, address, date, subject
Messageids are unique but not adjacent. But the other fields can have
duplicates. When I do "order by" I include the messageid as well, this
probably allows the method to work.
So I could try something like:
(if list was ordered by date and messageid)
select * from table where criteria=xxxxx and messageid > currentmessageid
order by date, messageid limit 1;
or if ordered by descending date and messageid:
select * from table where criteria=xxxxx and messageid < currentmessageid
order by date desc, messageid desc limit 1;
Similar for subject and the other stuff.
If I'm looking for previous and next I'll have to do the two selects.
They're limited to one row each, but I wonder if that would still be faster
than a full select.
Thanks,
Link.
From | Date | Subject | |
---|---|---|---|
Next Message | Holger Klawitter | 2000-06-23 08:09:37 | just a note |
Previous Message | Dustin Sallings | 2000-06-23 07:31:25 | Re: How to select a row and an adjacent row? |