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
signature.asc
Description: This is a digitally signed message part