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

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