Re: How to select a row and an adjacent row?
Re: How to select a row and an adjacent row?
От:
Lincoln Yeoh <lylyeoh@mecomb.com>
Дата:
Hi everyone, 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 :(. Is there a way to do it faster than doing the entire select? The reason I have this is because I'm letting the user choose the order of the query. When viewing information based on a particular row, I want to put a "Next" and/or "Prev" button to go to view information based on the next/prev row in the order previously selected, and so on... Storing the query results into a temporary location is starting to look like the way to do it. But as this is a web application, deciding the optimal solution to when and how to delete the results is not easy for me :(. Thanks! Link.
Re: How to select a row and an adjacent row?
От:
Lincoln Yeoh <lylyeoh@mecomb.com>
Дата:
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 > > order by 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.
Re: How to select a row and an adjacent row?
От:
Lincoln Yeoh <lylyeoh@mecomb.com>
Дата:
At 04:03 PM 23-06-2000 +0800, Lincoln Yeoh wrote: >At 10:55 AM 23-06-2000 +0400, you wrote: >>You could probably do this: >> >>select * from yourtable where > >> order by 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. >(if list was ordered by date and messageid) >select * from table where criteria=xxxxx and messageid > currentmessageid > order by date, messageid limit 1; Ack! I am fuzzyminded today. That won't work. I think I'll go take a break now... Cheerio! Link.
Re: app-index in www.pgsql.com
От:
The Hermit Hacker <scrappy@hub.org>
Дата:
fixed, we hadn't known it was down ... we had moved over our database from
a v6.5.3 -> v7.0 , and onto a seperate machine then before, and missed
that script ...
On Fri, 23 Jun 2000 kaiq@realtyideas.com wrote:
> anybody have a copy of the app-index in pgsql.com site?
>
> I remember I saw something important thare, but forget what
> is it. And I really feel I need it now. Can somebody help?
>
> is there any business reason not to activate the app-index?
> if not, please make it alive? Please, it is really very very
> helpful -- good for both newbie, evaluators and experienced.
>
> if can not restore it, at least somebody give me a copy of
> the old page? somebody saved it? I should have saved it!
>
>
> thanks
>
> Kai
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
Re: app-index in www.pgsql.com
От:
The Hermit Hacker <scrappy@hub.org>
Дата:
Fixed too, thanks ...
On Fri, 23 Jun 2000 kaiq@realtyideas.com wrote:
> thanks!!! I got the key words and got the site(s).
>
> P.S. there is a moreinfo link to "full.cgi", not working,
> but does not matter to me :-)
>
> thanks again
>
> Kai
>
> On Fri, 23 Jun 2000, The Hermit Hacker wrote:
>
> >
> > fixed, we hadn't known it was down ... we had moved over our database from
> > a v6.5.3 -> v7.0 , and onto a seperate machine then before, and missed
> > that script ...
> >
> >
> >
> > On Fri, 23 Jun 2000 kaiq@realtyideas.com wrote:
> >
> > > anybody have a copy of the app-index in pgsql.com site?
> > >
> > > I remember I saw something important thare, but forget what
> > > is it. And I really feel I need it now. Can somebody help?
> > >
> > > is there any business reason not to activate the app-index?
> > > if not, please make it alive? Please, it is really very very
> > > helpful -- good for both newbie, evaluators and experienced.
> > >
> > > if can not restore it, at least somebody give me a copy of
> > > the old page? somebody saved it? I should have saved it!
> > >
> > >
> > > thanks
> > >
> > > Kai
> > >
> >
> > Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
> > Systems Administrator @ hub.org
> > primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
> >
>
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
Inheritance question
От:
Richard Harvey Chapman <hchapman@3gfp.com>
Дата:
If I have the following: CREATE TABLE grand_parent ( num INTEGER ); CREATE TABLE parent ( count INTEGER ) INHERITS (grand_parent); Can I define the following table? It doesn't work as written. CREATE TABLE child ( UNIQUE (num, count) ) INHERITS (parent); Thanks, R.
app-index in www.pgsql.com
От:
<kaiq@realtyideas.com>
Дата:
anybody have a copy of the app-index in pgsql.com site? I remember I saw something important thare, but forget what is it. And I really feel I need it now. Can somebody help? is there any business reason not to activate the app-index? if not, please make it alive? Please, it is really very very helpful -- good for both newbie, evaluators and experienced. if can not restore it, at least somebody give me a copy of the old page? somebody saved it? I should have saved it! thanks Kai
Re: app-index in www.pgsql.com
От:
<kaiq@realtyideas.com>
Дата:
thanks!!! I got the key words and got the site(s).
P.S. there is a moreinfo link to "full.cgi", not working,
but does not matter to me :-)
thanks again
Kai
On Fri, 23 Jun 2000, The Hermit Hacker wrote:
>
> fixed, we hadn't known it was down ... we had moved over our database from
> a v6.5.3 -> v7.0 , and onto a seperate machine then before, and missed
> that script ...
>
>
>
> On Fri, 23 Jun 2000 kaiq@realtyideas.com wrote:
>
> > anybody have a copy of the app-index in pgsql.com site?
> >
> > I remember I saw something important thare, but forget what
> > is it. And I really feel I need it now. Can somebody help?
> >
> > is there any business reason not to activate the app-index?
> > if not, please make it alive? Please, it is really very very
> > helpful -- good for both newbie, evaluators and experienced.
> >
> > if can not restore it, at least somebody give me a copy of
> > the old page? somebody saved it? I should have saved it!
> >
> >
> > thanks
> >
> > Kai
> >
>
> Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
> Systems Administrator @ hub.org
> primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
>
Re: How to select a row and an adjacent row?
От:
Dustin Sallings <dustin@spy.net>
Дата:
On Fri, 23 Jun 2000, Lincoln Yeoh wrote: I do this same type of thing in my photo album (http://bleu.west.spy.net/~dustin/photo/) and that's exactly what I do, store the result set. Since the data can change while users are looking at it (more data can be inserted that would show up in arbitrary places in search results), the only way it could make sense to the end-user is for me to let the user flip through his own result set. This is done in a servlet, so it's a little easier to do that than it would be in a straight CGI, but it could be done anywhere. # Hi everyone, # # 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 :(. # # Is there a way to do it faster than doing the entire select? # # The reason I have this is because I'm letting the user choose the order of # the query. When viewing information based on a particular row, I want to # put a "Next" and/or "Prev" button to go to view information based on the # next/prev row in the order previously selected, and so on... # # Storing the query results into a temporary location is starting to look # like the way to do it. But as this is a web application, deciding the # optimal solution to when and how to delete the results is not easy for me :(. # # Thanks! # Link. # # -- dustin sallings The world is watching America, http://2852210114/~dustin/ and America is watching TV.
