Thread: 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