Re: How to do? - Mailing list pgsql-general

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