Re: How to do?

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