FW: How to do?

Sorrym didn't post this one to the list :P

On Fri, 2003-08-01 at 20:05, Franco Bruno Borghesi 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.
>
>
>
> 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