pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Limit 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