Thread: help on SQL
hello everyone, is it possible to get every other row in the table? like for example: data ----- 1 2 3 4 5 6 7 Is it possible to have this result (interval of 3)? result ------ 1 4 7 thanks, kenneth --
On Wed, 21 Apr 2004, Kenneth Suralta wrote: > > hello everyone, > > is it possible to get every other row in the table? > like for example: > data > ----- > 1 > 2 > 3 > 4 > 5 > 6 > 7 SELECT t.fields FROM table t WHERE data NOT IN (SELECT t2.data FROM tablet2 WHERE some_whereclause); it will select these rows which ones are not in the subquery. but you can do it simply by getting the negate of the whereclause... > > Is it possible to have this result (interval of 3)? > > result > ------ > 1 > 4 > 7 i don't see what you mean. please describe it. > > thanks, > kenneth > > -- > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > Bye, Gergely Czuczy mailto: phoemix@harmless.hu PGP: http://phoemix.harmless.hu/phoemix.pgp "Wish a god, a star, to believe in, With the realm of king of fantasy..."
hi gergely,<br /><br /> thanks for replying..<br /> what i mean is supposing there is a table with the following rows:<br/><pre wrap=""><tt>data <span class="moz-txt-citetags"></span>----- 1 2 3 4 5 6 7 is it possible to get every other N-row in the table? example, if N is 3. data ----- 1 4 7 thanks, kenneth </tt></pre><br /><br /> Gergely Czuczy wrote:<br /><blockquote cite="midPine.LNX.4.44.0404210907250.28604-100000@localhost"type="cite"><pre wrap="">On Wed, 21 Apr 2004, Kenneth Suraltawrote: </pre><blockquote type="cite"><pre wrap="">hello everyone, is it possible to get every other row in the table? like for example: data ----- 1 2 3 4 5 6 7 </pre></blockquote><pre wrap="">SELECT t.fields FROM table t WHERE data NOT IN (SELECT t2.data FROM tablet2 WHERE some_whereclause); it will select these rows which ones are not in the subquery. but you can do it simply by getting the negate of the whereclause... </pre><blockquote type="cite"><pre wrap="">Is it possible to havethis result (interval of 3)? result ------ 1 4 7 </pre></blockquote><pre wrap="">i don't see what you mean. please describe it. </pre><blockquote type="cite"><pre wrap="">thanks, kenneth -- ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster </pre></blockquote><pre wrap=""> Bye, Gergely Czuczy mailto: <a class="moz-txt-link-abbreviated" href="mailto:phoemix@harmless.hu">phoemix@harmless.hu</a> PGP: <a class="moz-txt-link-freetext" href="http://phoemix.harmless.hu/phoemix.pgp">http://phoemix.harmless.hu/phoemix.pgp</a> "Wish a god, a star, to believe in, With the realm of king of fantasy..." ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to <a class="moz-txt-link-abbreviated"href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a>) </pre></blockquote><br /><div class="moz-signature">-- <br /><img border="0" src="cid:part1.02090701.07000506@hq.ntsp.nec.co.jp"/></div>
ahh, i see, you need every Nth result from the given query. I think there is no way for this, but getting all the result and dropping from the client every not-Nthm but i may be wrong On Wed, 21 Apr 2004, Kenneth Suralta wrote: > hi gergely, > > thanks for replying.. > what i mean is supposing there is a table with the following rows: > > data > ----- > 1 > 2 > 3 > 4 > 5 > 6 > 7 > > is it possible to get every other N-row in the table? > example, if N is 3. > data > ----- > 1 > 4 > 7 > > thanks, > kenneth > > > > Gergely Czuczy wrote: > > On Wed, 21 Apr 2004, Kenneth Suralta wrote: > > > > hello everyone, > > is it possible to get every other row in the table? > like for example: > data > ----- > 1 > 2 > 3 > 4 > 5 > 6 > 7 > > > SELECT t.fields FROM table t WHERE data NOT IN (SELECT t2.data FROM table > t2 WHERE some_whereclause); > > it will select these rows which ones are not in the subquery. but you can > do it simply by getting the negate of the whereclause... > > > Is it possible to have this result (interval of 3)? > > result > ------ > 1 > 4 > 7 > > > i don't see what you mean. please describe it. > > > thanks, > kenneth > > -- > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > > > Bye, > > Gergely Czuczy > mailto: phoemix@harmless.hu > PGP: http://phoemix.harmless.hu/phoemix.pgp > > "Wish a god, a star, to believe in, > With the realm of king of fantasy..." > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > > > -- > [IMAGE] > > Bye, Gergely Czuczy mailto: phoemix@harmless.hu PGP: http://phoemix.harmless.hu/phoemix.pgp "Wish a god, a star, to believe in, With the realm of king of fantasy..."
test=# create table data (data int); CREATE test=# insert into data values(1); INSERT 3531768 1 test=# insert into data values(2); INSERT 3531769 1 test=# insert into data values(3); INSERT 3531770 1 test=# insert into data values(4); INSERT 3531771 1 test=# insert into data values(5); INSERT 3531772 1 test=# insert into data values(6); INSERT 3531773 1 test=# insert into data values(7); INSERT 3531774 1 test=# select * from data where (data - 1) % 3 = 0;data ------ 1 4 7 (3 rows) test=# HTH Michael Web Applications Developer Open World Ltd, 11 Riverside Court, Riverside Road, Bath, BA2 3DZ. Tel: +44 1225 444950 Fax: +44 1225 336738 http://www.openworld.co.uk/ CONFIDENTIALITY NOTICE The information contained in this message is confidential, intended only for the use of the individual or the entity named as recipient. If the reader of this message is not that recipient, you are notified that any dissemination, distribution or copy of this message is strictly prohibited. If you have received this message in error, please immediately notify us by telephone on the number above. Your co-operation is appreciated. On Wed, 21 Apr 2004, Kenneth Suralta wrote: > hi gergely, > > thanks for replying.. > what i mean is supposing there is a table with the following rows: > > data > ----- > 1 > 2 > 3 > 4 > 5 > 6 > 7 > > is it possible to get every other N-row in the table? > example, if N is 3. > data > ----- > 1 > 4 > 7 > > thanks, > kenneth > > > > Gergely Czuczy wrote: > > On Wed, 21 Apr 2004, Kenneth Suralta wrote: > > > > hello everyone, > > is it possible to get every other row in the table? > like for example: > data > ----- > 1 > 2 > 3 > 4 > 5 > 6 > 7 > > > SELECT t.fields FROM table t WHERE data NOT IN (SELECT t2.data FROM table > t2 WHERE some_whereclause); > > it will select these rows which ones are not in the subquery. but you can > do it simply by getting the negate of the whereclause... > > > Is it possible to have this result (interval of 3)? > > result > ------ > 1 > 4 > 7 > > > i don't see what you mean. please describe it. > > > thanks, > kenneth > > -- > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > > > Bye, > > Gergely Czuczy > mailto: phoemix@harmless.hu > PGP: http://phoemix.harmless.hu/phoemix.pgp > > "Wish a god, a star, to believe in, > With the realm of king of fantasy..." > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > > > -- > [IMAGE] > >
hello what you have done is not the thing that was asked. you've retrieved every row that has a spacial value(data-1 %3). the request was to retrieve every 3rd row. the difference is that, the 'every Nth' row has nothing to do with the value of any field in the row. try to retrieve every Nth row from a query where there are no integer values, only timestamps and string. or try to do your method where the integer value is not continous. On Wed, 21 Apr 2004, Michael Stephenson wrote: > test=# create table data (data int); > CREATE > test=# insert into data values(1); > INSERT 3531768 1 > test=# insert into data values(2); > INSERT 3531769 1 > test=# insert into data values(3); > INSERT 3531770 1 > test=# insert into data values(4); > INSERT 3531771 1 > test=# insert into data values(5); > INSERT 3531772 1 > test=# insert into data values(6); > INSERT 3531773 1 > test=# insert into data values(7); > INSERT 3531774 1 > test=# select * from data where (data - 1) % 3 = 0; > data > ------ > 1 > 4 > 7 > (3 rows) > > test=# > > HTH > > Michael > > Web Applications Developer > Open World Ltd, 11 Riverside Court, Riverside Road, Bath, BA2 3DZ. > Tel: +44 1225 444950 Fax: +44 1225 336738 http://www.openworld.co.uk/ > > CONFIDENTIALITY NOTICE > The information contained in this message is confidential, intended only for > the use of the individual or the entity named as recipient. If the reader of > this message is not that recipient, you are notified that any > dissemination, > distribution or copy of this message is strictly prohibited. If you have > received this message in error, please immediately notify us by telephone on > the number above. Your co-operation is appreciated. > > On Wed, 21 Apr 2004, Kenneth Suralta wrote: > > > hi gergely, > > > > thanks for replying.. > > what i mean is supposing there is a table with the following rows: > > > > data > > ----- > > 1 > > 2 > > 3 > > 4 > > 5 > > 6 > > 7 > > > > is it possible to get every other N-row in the table? > > example, if N is 3. > > data > > ----- > > 1 > > 4 > > 7 > > > > thanks, > > kenneth > > > > > > > > Gergely Czuczy wrote: > > > > On Wed, 21 Apr 2004, Kenneth Suralta wrote: > > > > > > > > hello everyone, > > > > is it possible to get every other row in the table? > > like for example: > > data > > ----- > > 1 > > 2 > > 3 > > 4 > > 5 > > 6 > > 7 > > > > > > SELECT t.fields FROM table t WHERE data NOT IN (SELECT t2.data FROM table > > t2 WHERE some_whereclause); > > > > it will select these rows which ones are not in the subquery. but you can > > do it simply by getting the negate of the whereclause... > > > > > > Is it possible to have this result (interval of 3)? > > > > result > > ------ > > 1 > > 4 > > 7 > > > > > > i don't see what you mean. please describe it. > > > > > > thanks, > > kenneth > > > > -- > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > > Bye, > > > > Gergely Czuczy > > mailto: phoemix@harmless.hu > > PGP: http://phoemix.harmless.hu/phoemix.pgp > > > > "Wish a god, a star, to believe in, > > With the realm of king of fantasy..." > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > > > > > > > > > -- > > [IMAGE] > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > Bye, Gergely Czuczy mailto: phoemix@harmless.hu PGP: http://phoemix.harmless.hu/phoemix.pgp "Wish a god, a star, to believe in, With the realm of king of fantasy..."
In postgres (or in SQL in general) there isn't a natural way of expressing
what you want to do.
I suppose you could do something like:
create temp sequence dataseq;
select data from data where nextval('dataseq') % 3 = 1;
drop sequence dataseq;
Which'll do the job, but is a bit nasty.
Michael
Web Applications Developer
Open World Ltd, 11 Riverside Court, Riverside Road, Bath, BA2 3DZ.
Tel: +44 1225 444950 Fax: +44 1225 336738 http://www.openworld.co.uk/
CONFIDENTIALITY NOTICE
The information contained in this message is confidential, intended only for
the use of the individual or the entity named as recipient. If the reader of
this message is not that recipient, you are notified that any
dissemination,
distribution or copy of this message is strictly prohibited. If you have
received this message in error, please immediately notify us by telephone on
the number above. Your co-operation is appreciated.
On Wed, 21 Apr 2004, Gergely Czuczy wrote:
> hello
>
> what you have done is not the thing that was asked. you've retrieved every
> row that has a spacial value(data-1 %3). the request was to retrieve every
> 3rd row. the difference is that, the 'every Nth' row has nothing to do
> with the value of any field in the row. try to retrieve every Nth row from
> a query where there are no integer values, only timestamps and string. or
> try to do your method where the integer value is not continous.
>
> On Wed, 21 Apr 2004, Michael Stephenson wrote:
>
> > test=# create table data (data int);
> > CREATE
> > test=# insert into data values(1);
> > INSERT 3531768 1
> > test=# insert into data values(2);
> > INSERT 3531769 1
> > test=# insert into data values(3);
> > INSERT 3531770 1
> > test=# insert into data values(4);
> > INSERT 3531771 1
> > test=# insert into data values(5);
> > INSERT 3531772 1
> > test=# insert into data values(6);
> > INSERT 3531773 1
> > test=# insert into data values(7);
> > INSERT 3531774 1
> > test=# select * from data where (data - 1) % 3 = 0;
> > data
> > ------
> > 1
> > 4
> > 7
> > (3 rows)
> >
> > test=#
> >
> > HTH
> >
> > Michael
> >
> > Web Applications Developer
> > Open World Ltd, 11 Riverside Court, Riverside Road, Bath, BA2 3DZ.
> > Tel: +44 1225 444950 Fax: +44 1225 336738 http://www.openworld.co.uk/
> >
> > CONFIDENTIALITY NOTICE
> > The information contained in this message is confidential, intended only for
> > the use of the individual or the entity named as recipient. If the reader of
> > this message is not that recipient, you are notified that any
> > dissemination,
> > distribution or copy of this message is strictly prohibited. If you have
> > received this message in error, please immediately notify us by telephone on
> > the number above. Your co-operation is appreciated.
> >
> > On Wed, 21 Apr 2004, Kenneth Suralta wrote:
> >
> > > hi gergely,
> > >
> > > thanks for replying..
> > > what i mean is supposing there is a table with the following rows:
> > >
> > > data
> > > -----
> > > 1
> > > 2
> > > 3
> > > 4
> > > 5
> > > 6
> > > 7
> > >
> > > is it possible to get every other N-row in the table?
> > > example, if N is 3.
> > > data
> > > -----
> > > 1
> > > 4
> > > 7
> > >
> > > thanks,
> > > kenneth
> > >
> > >
> > >
> > > Gergely Czuczy wrote:
> > >
> > > On Wed, 21 Apr 2004, Kenneth Suralta wrote:
> > >
> > >
> > >
> > > hello everyone,
> > >
> > > is it possible to get every other row in the table?
> > > like for example:
> > > data
> > > -----
> > > 1
> > > 2
> > > 3
> > > 4
> > > 5
> > > 6
> > > 7
> > >
> > >
> > > SELECT t.fields FROM table t WHERE data NOT IN (SELECT t2.data FROM table
> > > t2 WHERE some_whereclause);
> > >
> > > it will select these rows which ones are not in the subquery. but you can
> > > do it simply by getting the negate of the whereclause...
> > >
> > >
> > > Is it possible to have this result (interval of 3)?
> > >
> > > result
> > > ------
> > > 1
> > > 4
> > > 7
> > >
> > >
> > > i don't see what you mean. please describe it.
> > >
> > >
> > > thanks,
> > > kenneth
> > >
> > > --
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
> > >
> > >
> > >
> > > Bye,
> > >
> > > Gergely Czuczy
> > > mailto: phoemix@harmless.hu
> > > PGP: http://phoemix.harmless.hu/phoemix.pgp
> > >
> > > "Wish a god, a star, to believe in,
> > > With the realm of king of fantasy..."
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > >
> > >
> > >
> > >
> > >
> > > --
> > > [IMAGE]
> > >
> > >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 7: don't forget to increase your free space map settings
> >
>
>
> Bye,
>
> Gergely Czuczy
> mailto: phoemix@harmless.hu
> PGP: http://phoemix.harmless.hu/phoemix.pgp
>
> "Wish a god, a star, to believe in,
> With the realm of king of fantasy..."
>
On Wed, Apr 21, 2004 at 12:21:32 +0200, Gergely Czuczy <phoemix@harmless.hu> wrote: > hello > > what you have done is not the thing that was asked. you've retrieved every > row that has a spacial value(data-1 %3). the request was to retrieve every > 3rd row. the difference is that, the 'every Nth' row has nothing to do > with the value of any field in the row. try to retrieve every Nth row from > a query where there are no integer values, only timestamps and string. or > try to do your method where the integer value is not continous. Rows do not have any order. Unless you define the ordering you want in terms of same data in the rows there is no every other row. If you can order the rows (no randomly breaking ties) then you can get the number of the row in this ordering (though not by a very efficient method) and select rows based on the this number.