Thread: How to do?
Hi, Have question.... How to do such like this: I have: select <column list> form <tables> where <where statement>; how to make one column be row numbers in result? and second one: have select like above.... and I know that in result is record with e.g. uid='AC13A1'. How to reduce result to this record and one record before in result and one record after in result? Are this questions just silly problems? :-) regards
On Wed, 2003-07-30 at 07:17, Robert Partyka wrote: > Hi, > > Have question.... > > How to do such like this: > > I have: select <column list> form <tables> where <where statement>; > how to make one column be row numbers in result? > > and second one: > have select like above.... and I know that in result is record with e.g. > uid='AC13A1'. > How to reduce result to this record and one record before in result and one > record after in result? > > Are this questions just silly problems? :-) No, but slightly ambiguous, at least for my old brain. -- +-----------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!" | | unknown | +-----------------------------------------------------------------+
On 1 Aug 2003 at 9:47, Ron Johnson wrote: > On Wed, 2003-07-30 at 07:17, Robert Partyka wrote: > > Hi, > > > > Have question.... > > > > How to do such like this: > > > > I have: select <column list> form <tables> where <where statement>; > > how to make one column be row numbers in result? select oid,name from a; It's oid. Of course you need to have them enabled since they are optional in recent versions.. > > > > and second one: > > have select like above.... and I know that in result is record with e.g. > > uid='AC13A1'. > > How to reduce result to this record and one record before in result and one > > record after in result? I didn't get that.. could you please elaborate? Bye Shridhar -- Not me, guy. I read the Bash man page each day like a Jehovah's Witness readsthe Bible. No wait, the Bash man page IS the bible. Excuse me...(More on confusing aliases, taken from comp.os.linux.misc)
I don't know if this is the best solution, but a temp sequence should work:
CREATE TEMP SEQUENCE tmp_seq;
SELECT
nextval('tmp_seq') AS row_num,
<column list>
FROM
<tables>
WHERE
<where statement>;
DROP SEQUENCE tmp_seq;
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?
PS: I don't think your questions are silly.
On Wed, 2003-07-30 at 07:17, Robert Partyka wrote:
CREATE TEMP SEQUENCE tmp_seq;
SELECT
nextval('tmp_seq') AS row_num,
<column list>
FROM
<tables>
WHERE
<where statement>;
DROP SEQUENCE tmp_seq;
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?
PS: I don't think your questions are silly.
On Wed, 2003-07-30 at 07:17, Robert Partyka wrote:
Hi,Have question....How to do such like this:I have: select <column list> form <tables> where <where statement>;how to make one column be row numbers in result?and second one:have select like above.... and I know that in result is record with e.g. uid='AC13A1'.How to reduce result to this record and one record before in result and one record after in result?Are this questions just silly problems? :-)
Attachment
On Fri, 2003-08-01 at 09:56, Shridhar Daithankar wrote: > On 1 Aug 2003 at 9:47, Ron Johnson wrote: > > > On Wed, 2003-07-30 at 07:17, Robert Partyka wrote: > > > Hi, > > > > > > Have question.... > > > > > > How to do such like this: > > > > > > I have: select <column list> form <tables> where <where statement>; > > > how to make one column be row numbers in result? > > select oid,name from a; > > It's oid. Of course you need to have them enabled since they are optional in > recent versions.. Oh, *that* row number... -- +-----------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!" | | unknown | +-----------------------------------------------------------------+
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
On Fri, 2003-08-01 at 11: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 :) But relational algebra and SQL don't have the concept of row numbers. "row numbers" like OIDs are internal constructs that are exposed to the users, but shouldn't be used. > 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 -- +-----------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!" | | unknown | +-----------------------------------------------------------------+
Sorrym didn't post this one to the list :P
On Fri, 2003-08-01 at 20:05, Franco Bruno Borghesi wrote:
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
Attachment
On Fri, 2003-08-01 at 11: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 :) So you want to pass query result sets between connections, so that each subsequent connection can query the previous connection's results? > 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 -- +-----------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!" | | unknown | +-----------------------------------------------------------------+
В Сбт, 02.08.2003, в 01:55, Ron Johnson пишет:
> On Fri, 2003-08-01 at 11: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 :)
>
> But relational algebra and SQL don't have the concept of row numbers.
> "row numbers" like OIDs are internal constructs that are exposed to
> the users, but shouldn't be used.
There's a trick if you have a unique sortable column in the table, it
goes like this:
create table a (id serial primary key, data text);
insert into a values (default, 'fds');
insert into a values (default, 'fdsas');
insert into a values (default, 'sas');
insert into a values (default, 'asf');
select * from a;
id | data
----+-------
1 | fds
2 | fdsas
3 | sas
4 | asf
(записей: 4)
(select count(1) from a where a.data <= b.data) as rownumber, * from a b order by data;
rownumber | id | data
-----------+----+-------
1 | 4 | asf
2 | 1 | fds
3 | 2 | fdsas
4 | 3 | sas
(записей: 4)
--
Markus Bertheau <twanger@bluetwanger.de>
Attachment
At 16:14 03-08-02 -0500, you wrote: >On Fri, 2003-08-01 at 11: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 :) > >So you want to pass query result sets between connections, so that >each subsequent connection can query the previous connection's >results? Rather i select result on one connection and pass identification information of one field to next connection. > > 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 > >-- >+-----------------------------------------------------------------+ >| Ron Johnson, Jr. Home: ron.l.johnson@cox.net | >| Jefferson, LA USA | >| | >| "I'm not a vegetarian because I love animals, I'm a vegetarian | >| because I hate vegetables!" | >| unknown | >+-----------------------------------------------------------------+ > > > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster
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
On Tue, 2003-08-05 at 06:59, Robert Partyka wrote: > At 16:14 03-08-02 -0500, you wrote: > >On Fri, 2003-08-01 at 11: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 :) > > > >So you want to pass query result sets between connections, so that > >each subsequent connection can query the previous connection's > >results? > > Rather i select result on one connection and pass identification > information of one field to next connection. You want to pass the OIDs of a result set from conn1 to conn2? > > > 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 :) -- +-----------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!" | | unknown | +-----------------------------------------------------------------+
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:
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