pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLimit rows
8+ messages / 6 participants
[nested] [flat]
* Limit rows
@ 1999-09-29 15:09 Mark Jewiss <Mark.Jewiss@knowledge.com>
0 siblings, 0 replies; 8+ messages in thread
From: Mark Jewiss @ 1999-09-29 15:09 UTC (permalink / raw)
To: pgsql-sql
Hello,
I'm still thinking about ways to extract specific rows of data from a
table, whilst ignoring others. i.e. I want to see rows 3-7 inclusive from
a table.
Assume that I don't have a numerical identifier - I do, but I can't use
that as rows from this table may be deleted at some point, thereby
knocking the id row out of sequence.
Basically I want to be able to do something like this:
select * from table ignore 10 limit 5;
I can't use cursors, which are ideal for this I know.
Is there such a thing in SQL or pgsql specifically? If not I'll have to
write something....
Regards,
Mark.
--
Mark Jewiss
Knowledge Matters Limited
http://www.knowledge.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Limit rows
@ 1999-09-29 15:17 Mark Jewiss <Mark.Jewiss@knowledge.com>
1999-09-29 15:56 ` Re: [SQL] Limit rows Oleg Bartunov <oleg@sai.msu.su>
1999-09-29 16:21 ` Re: [SQL] Limit rows Stuart Rison <rison@biochemistry.ucl.ac.uk>
1999-09-29 17:19 ` Re: [SQL] Limit rows Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 3 replies; 8+ messages in thread
From: Mark Jewiss @ 1999-09-29 15:17 UTC (permalink / raw)
To: pgsql-sql
Hello,
I'm still thinking about ways to extract specific rows of data from a
table, whilst ignoring others. i.e. I want to see rows 3-7 inclusive from
a table.
Assume that I don't have a numerical identifier - I do, but I can't use
that as rows from this table may be deleted at some point, thereby
knocking the id row out of sequence.
Basically I want to be able to do something like this:
select * from table ignore 10 limit 5;
I can't use cursors, which are ideal for this I know.
Is there such a thing in SQL or pgsql specifically? If not I'll have to
write something....
Regards,
Mark.
--
Mark Jewiss
Knowledge Matters Limited
http://www.knowledge.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] Limit rows
1999-09-29 15:17 Limit rows Mark Jewiss <Mark.Jewiss@knowledge.com>
@ 1999-09-29 15:56 ` Oleg Bartunov <oleg@sai.msu.su>
1999-09-29 16:09 ` Re: [SQL] Limit rows Mark Jewiss <Mark.Jewiss@knowledge.com>
2 siblings, 1 reply; 8+ messages in thread
From: Oleg Bartunov @ 1999-09-29 15:56 UTC (permalink / raw)
To: Mark Jewiss <Mark.Jewiss@knowledge.com>; +Cc: pgsql-sql
Mark,
did you try
select * from table limit 5,10;
Or I misunderstood you
Oleg
On Wed, 29 Sep 1999, Mark Jewiss wrote:
> Date: Wed, 29 Sep 1999 16:17:03 +0100 (BST)
> From: Mark Jewiss <Mark.Jewiss@knowledge.com>
> To: pgsql-sql@postgreSQL.org
> Subject: [SQL] Limit rows
>
> Hello,
>
> I'm still thinking about ways to extract specific rows of data from a
> table, whilst ignoring others. i.e. I want to see rows 3-7 inclusive from
> a table.
>
> Assume that I don't have a numerical identifier - I do, but I can't use
> that as rows from this table may be deleted at some point, thereby
> knocking the id row out of sequence.
>
> Basically I want to be able to do something like this:
>
> select * from table ignore 10 limit 5;
>
> I can't use cursors, which are ideal for this I know.
>
> Is there such a thing in SQL or pgsql specifically? If not I'll have to
> write something....
>
> Regards,
>
> Mark.
> --
> Mark Jewiss
> Knowledge Matters Limited
> http://www.knowledge.com
>
>
>
>
> ************
>
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] Limit rows
1999-09-29 15:17 Limit rows Mark Jewiss <Mark.Jewiss@knowledge.com>
1999-09-29 15:56 ` Re: [SQL] Limit rows Oleg Bartunov <oleg@sai.msu.su>
@ 1999-09-29 16:09 ` Mark Jewiss <Mark.Jewiss@knowledge.com>
1999-09-29 23:36 ` Re: [SQL] Limit rows Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Mark Jewiss @ 1999-09-29 16:09 UTC (permalink / raw)
To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: pgsql-sql
Hello,
On Wed, 29 Sep 1999, Oleg Bartunov wrote:
> select * from table limit 5,10;
Excellent, that works perfectly. I've done a grep over the html
documentation, and can't see anything regarding the limit feature (if I've
just missed it and it is there could someone provide a pointer please?).
I'm interested now in how this method actually works - is a query
performed that reads all of the rows of a table into memory, and then the
non-requested rows are discarded before the results are sent back?
I'm interested in how this would work with a massive table......
Regards,
Mark.
--
Mark Jewiss
Knowledge Matters Limited
http://www.knowledge.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] Limit rows
1999-09-29 15:17 Limit rows Mark Jewiss <Mark.Jewiss@knowledge.com>
1999-09-29 15:56 ` Re: [SQL] Limit rows Oleg Bartunov <oleg@sai.msu.su>
1999-09-29 16:09 ` Re: [SQL] Limit rows Mark Jewiss <Mark.Jewiss@knowledge.com>
@ 1999-09-29 23:36 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 1999-09-29 23:36 UTC (permalink / raw)
To: Mark Jewiss <Mark.Jewiss@knowledge.com>; +Cc: Oleg Bartunov <oleg@sai.msu.su>; pgsql-sql
Mark Jewiss <Mark.Jewiss@knowledge.com> writes:
> [ re LIMIT ]
> I'm interested now in how this method actually works - is a query
> performed that reads all of the rows of a table into memory, and then the
> non-requested rows are discarded before the results are sent back?
> I'm interested in how this would work with a massive table......
The executor will stop generating rows as soon as it's satisfied the
limit+offset. Whether that's actually quick depends on your query;
for example, if you do something that requires an explicit sort step,
the full sort has to be done anyway (since there's no way to tell
which rows it'd return first without finishing the sort...). You can
use EXPLAIN if you're not sure whether a query will use a sort.
Also, a large offset and a small limit might not be as fast as you'd
like, since the rows discarded by OFFSET will be generated and then
dropped. So, you should not consider this feature as a substitute for a
cursor. If you intend to fetch most of a table a little bit at a time,
you want to use DECLARE CURSOR and FETCH.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] Limit rows
1999-09-29 15:17 Limit rows Mark Jewiss <Mark.Jewiss@knowledge.com>
@ 1999-09-29 16:21 ` Stuart Rison <rison@biochemistry.ucl.ac.uk>
2 siblings, 0 replies; 8+ messages in thread
From: Stuart Rison @ 1999-09-29 16:21 UTC (permalink / raw)
To: Mark Jewiss <Mark.Jewiss@knowledge.com>; +Cc: pgsql-sql
So long as you have a numerical identifier for the 'first' row you are
interested in you should be able to do something like:
SELECT * FROM TABLE WHERE numerical_field>=23 ORDER BY
numerical_field LIMIT 5;
This will pick the row 23 and the next 4 rows regardless of any gaps in
the sequence.
HTH,
Stuart
On Wed, 29 Sep 1999, Mark Jewiss wrote:
> Hello,
>
> I'm still thinking about ways to extract specific rows of data from a
> table, whilst ignoring others. i.e. I want to see rows 3-7 inclusive from
> a table.
>
> Assume that I don't have a numerical identifier - I do, but I can't use
> that as rows from this table may be deleted at some point, thereby
> knocking the id row out of sequence.
>
> Basically I want to be able to do something like this:
>
> select * from table ignore 10 limit 5;
>
> I can't use cursors, which are ideal for this I know.
>
> Is there such a thing in SQL or pgsql specifically? If not I'll have to
> write something....
>
> Regards,
>
> Mark.
> --
> Mark Jewiss
> Knowledge Matters Limited
> http://www.knowledge.com
>
>
>
>
> ************
>
Stuart C. G. Rison
Department of Biochemistry and Molecular Biology
6th floor, Darwin Building, University College London (UCL)
Gower Street, London, WC1E 6BT, United Kingdom
Tel. 0207 504 2303, Fax. 0207 380 7033
e-mail: rison@biochem.ucl.ac.uk
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] Limit rows
1999-09-29 15:17 Limit rows Mark Jewiss <Mark.Jewiss@knowledge.com>
@ 1999-09-29 17:19 ` Bruce Momjian <maillist@candle.pha.pa.us>
2 siblings, 0 replies; 8+ messages in thread
From: Bruce Momjian @ 1999-09-29 17:19 UTC (permalink / raw)
To: Mark Jewiss <Mark.Jewiss@knowledge.com>; +Cc: pgsql-sql
> Hello,
>
> I'm still thinking about ways to extract specific rows of data from a
> table, whilst ignoring others. i.e. I want to see rows 3-7 inclusive from
> a table.
SELECT * FROM table LIMIT 4 OFFSET 3
Should be in 6.5.*.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: [SQL] Limit rows
@ 1999-09-29 17:17 Daniel Péder <dpeder@infoset.cz>
0 siblings, 0 replies; 8+ messages in thread
From: Daniel Péder @ 1999-09-29 17:17 UTC (permalink / raw)
To: pgsql-sql
Yes, in the 6.x.x. manual is the:
select * from mytab
[ LIMIT { count | ALL } [ { OFFSET | , } count ] ] ;
-----Original Message-----
From: Mark Jewiss [SMTP:Mark.Jewiss@knowledge.com]
Sent: Wednesday, September 29, 1999 4:17 PM
To: pgsql-sql@postgreSQL.org
Subject: [SQL] Limit rows
Hello,
I'm still thinking about ways to extract specific rows of data from a
table, whilst ignoring others. i.e. I want to see rows 3-7 inclusive from
a table.
Assume that I don't have a numerical identifier - I do, but I can't use
that as rows from this table may be deleted at some point, thereby
knocking the id row out of sequence.
Basically I want to be able to do something like this:
select * from table ignore 10 limit 5;
I can't use cursors, which are ideal for this I know.
Is there such a thing in SQL or pgsql specifically? If not I'll have to
write something....
Regards,
Mark.
--
Mark Jewiss
Knowledge Matters Limited
http://www.knowledge.com
************
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~1999-09-29 23:36 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-09-29 15:09 Limit rows Mark Jewiss <Mark.Jewiss@knowledge.com>
1999-09-29 15:17 Limit rows Mark Jewiss <Mark.Jewiss@knowledge.com>
1999-09-29 15:56 ` Oleg Bartunov <oleg@sai.msu.su>
1999-09-29 16:09 ` Mark Jewiss <Mark.Jewiss@knowledge.com>
1999-09-29 23:36 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-09-29 16:21 ` Stuart Rison <rison@biochemistry.ucl.ac.uk>
1999-09-29 17:19 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-09-29 17:17 RE: [SQL] Limit rows Daniel Péder <dpeder@infoset.cz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox