Re: How to do?

mmmhhhh... I don't understand. The query brings a resultset just like
the one you asked.

When you say that 'there's no guarantee that A field is sorted or
unique...', what do you mean? The query doesn't care about the "A"
field, it just needs "UID" to be a candidate key.

And I still don't understand what you need the row number for...

On Tue, 2003-08-05 at 09:04, Robert Partyka wrote:

> At 20:05 03-08-01 -0300, you wrote:
> >This is the best I could come up with:
> >
> >SELECT
> > F1.a, F1.b, F1.uid
> >FROM
> > foo F1
> > LEFT JOIN (
> > SELECT uid FROM foo WHERE a>=(SELECT a FROM foo WHERE uid='AC88')
> > AND uid<>'AC88' ORDER BY a LIMIT 1
> > ) F2 ON (F2.uid=F1.uid)
> > LEFT JOIN (
> > SELECT uid FROM foo WHERE a<=(SELECT a FROM foo WHERE uid='AC88')
> > AND uid<>'AC88' ORDER BY a LIMIT 1
> > ) F3 ON (F3.uid=F1.uid)
> >WHERE
> > F1.uid='AC88' OR
> > F2.uid IS NOT NULL OR
> > F3.uid IS NOT NULL
> >
> >I don't know how this query perfroms, but I'm sure it works :)
> >
> >Explained:
> >-F2 has the first record *after* AC88.
> >-F3 has the first record *before* AC88
> >-The condition (the main WHERE) asks for the AC88 record itsself, or any
> >record where uid is not null (which are the ones brought by the left joins).
> >
> >Hope it helps... if it does not, ask again.
> >
>
> Almost it, but - there's no guarantee that A field is sorted or unique...
> :) because of that I ask how to get row number :)
>
>
>
> >On Fri, 2003-08-01 at 13:44, Robert Partyka wrote:
> >>
> >>Ron Johnson wrote:
> >> > No, but slightly ambiguous, at least for my old brain.
> >>I will try to by more unequivocal this time :)
> >>
> >>Shridhar Daithankar wrote:
> >> > select oid,name from a;
> >>I know it, but i have to have not oid's but row numbers :) such like :
> >> table "test"
> >> offset | value
> >>-----------+------------
> >>1 | AC43
> >>2 | AC4X
> >>3 | AX43
> >>4 | ACX3
> >>....
> >>n | XC4A
> >>
> >>the best will be without using sequence :)
> >>
> >>Shridhar Daithankar wrote:
> >> > I didn't get that.. could you please elaborate?
> >>
> >>Franco Bruno Borghesi wrote:
> >> > And about the rows before and after that you ask, I don't understand...
> >>based on what you mean
> >> > *before* and *after*? you don't have an order by clause.
> >>
> >> > And what do you mean with "I know that in result is record with e.g.
> >>uid='AC13A1'"?
> >> > You know this uid *before* sending the query? is it part of your <where
> >>statement>? can you use
> >> > this value as a hard coded condition for a subquery?
> >>
> >>Ok, so its goes something like that:
> >>
> >>lets say i have select query: select a,b,uid from foo where c='bar' order
> >>by a;
> >>
> >>with results like that:
> >> a | b | uid
> >>----+----+------
> >>2 |x | AC01
> >>2 |w | AC43
> >>4 |d | AC88
> >>4 |a | AC13
> >>...
> >>7 |c | AC22
> >>
> >>
> >>and lets say I selected this before and I know that there is uid='AC88';
> >>
> >>and in another connection (in lets say next requested www php script )
> >>without selecting all
> >>this data or even full list of only uid`s and making sequence scan row by
> >>row I wont to get
> >>something like that from select I have write above:
> >>
> >> a | b | uid
> >>----+----+------
> >>2 |w | AC43
> >>4 |d | AC88
> >>4 |a | AC13
> >>(3 rows)
> >>
> >>if there is row before and row next of uid='AC88' or
> >>
> >> a | b | uid
> >>----+----+------
> >>2 |w | AC43
> >>4 |d | AC88
> >>(2 rows)
> >>if uid='AC88' is last one row
> >>
> >>or
> >>
> >> a | b | uid
> >>----+----+------
> >>4 |d | AC88
> >>4 |a | AC13
> >>(2 rows)
> >>if uid='AC88' is first row
> >>
> >>I hope its more understandable than before :)
> >>
> >>regards
> >>Robert 'BoBsoN' Partyka
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 7: don't forget to increase your free space map settings
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>