Обсуждение: 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

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

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

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

> 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,
Pennsylvania19026
 

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