Thread: fetching rows

fetching rows

From
"Nikolay Mijaylov"
Date:
Let say we have a select that returns 100 rows.

I can fetch first 25 with simple sql:

BEGIN WORK;
DECLARE liahona CURSOR FOR SELECT * FROM films;
FETCH [FORWARD] 25 IN liahona;
CLOSE liahona;
COMMIT WORK;

but how I can fetch rows from 26 to 50? I mean withou fetching first 25. Or
can I skip first 25?

When i ask this you need to know that Im using PHP in web environment and
try do this to get more  performens from the script (now the script fetch
all rows, then jump to needed row)




Re: fetching rows

From
"K Parker"
Date:
I hate to be the bearer of bad news, but if you're using PHP and you wanted to fetch just 25 rows at a time for a
singlepage, and then fetch more when the user clicks on a NEXT button or link, you're completely out of luck.  Each
httptransaction is completely separate and so you can't maintain a cursor between pages.  Perhaps LIMIT would help you
here;but I've found it more useful to make sure there is a unique key for the order I'm displaying, and then say "WHERE
key> highest_key_value_on_current_page"
 




Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at http://www.eudoramail.com


Re: fetching rows

From
Jeff Hoffmann
Date:
Nikolay Mijaylov wrote:
> 
> Let say we have a select that returns 100 rows.
> 
> I can fetch first 25 with simple sql:
> 
> BEGIN WORK;
> DECLARE liahona CURSOR FOR SELECT * FROM films;
> FETCH [FORWARD] 25 IN liahona;
> CLOSE liahona;
> COMMIT WORK;
> 
> but how I can fetch rows from 26 to 50? I mean withou fetching first 25. Or
> can I skip first 25?

you can't do that with a cursor, but you can use they mysql-ism called a
limit clause.  for example, to fetch rows 26-50 from that query, you'd
do:

select * from films limit 25,26;

or

select * from files limit 25 offset 26;

-- 

Jeff Hoffmann
PropertyKey.com


Re: fetching rows

From
Martin Christensen
Date:
>>>>> "Jeff" == Jeff Hoffmann <jeff@propertykey.com> writes:
Jeff> you can't do that with a cursor, but you can use they mysql-ism
Jeff> called a limit clause.  for example, to fetch rows 26-50 from
Jeff> that query, you'd do:
Jeff> select * from films limit 25,26;
Jeff> or
Jeff> select * from files limit 25 offset 26;

Since there's no particular ordering of the output of such a query it
is necessary to explicitly state an ordering key. Otherwise you'll
quickly find that the phase of the moon has very significant influence
on the produced results. :-) Especially in 7.*, as I understand it.

Martin

-- 
GPG public key: http://home1.stofanet.dk/factotum/gpgkey.txt


Re: fetching rows

From
Decio Fonini
Date:
Hello, Nikolay,

Don't use cursors; instead, use:
" select * from films limit 25 offset 0 ; "

and on the next query:
" select * from films limit 50 offset 25 ; " 

and so on. You have to encode the current offset into the NEXT link,
either making it into a button inside a form, with a hidden field
containing the offset, or making it an HREF with URL-encoded data
(HREF="myform.php?mycounter=50").

I don't remember in which version the LIMIT option appeared; if your
version doesn't support it, move on to 7.0.2!

Have fun!

On Mon, 30 Oct 2000, Nikolay Mijaylov wrote:

> Let say we have a select that returns 100 rows.
> 
> I can fetch first 25 with simple sql:
> 
> BEGIN WORK;
> DECLARE liahona CURSOR FOR SELECT * FROM films;
> FETCH [FORWARD] 25 IN liahona;
> CLOSE liahona;
> COMMIT WORK;
> 
> but how I can fetch rows from 26 to 50? I mean withou fetching first 25. Or
> can I skip first 25?
> 
> When i ask this you need to know that Im using PHP in web environment and
> try do this to get more  performens from the script (now the script fetch
> all rows, then jump to needed row)
> 
> 

-------------------------------------------------------
Fight for a free world: no walls, no windows, no gates.



Re: fetching rows

From
"Robert B. Easter"
Date:
On Monday 30 October 2000 14:02, Jeff Hoffmann wrote:
> Nikolay Mijaylov wrote:
> > Let say we have a select that returns 100 rows.
> >
> > I can fetch first 25 with simple sql:
> >
> > BEGIN WORK;
> > DECLARE liahona CURSOR FOR SELECT * FROM films;
> > FETCH [FORWARD] 25 IN liahona;
> > CLOSE liahona;
> > COMMIT WORK;
> >
> > but how I can fetch rows from 26 to 50? I mean withou fetching first 25.
> > Or can I skip first 25?
>

I've done some web pages that have paging.  It did it with DECLARE to make a 
cursor then I used PostgreSQL's non-standard MOVE SQL command to start 
FETCHing from some offset depending on the page number.


> you can't do that with a cursor, but you can use they mysql-ism called a
> limit clause.  for example, to fetch rows 26-50 from that query, you'd
> do:
>
> select * from films limit 25,26;
>
> or
>
> select * from files limit 25 offset 26;

-- 
-------- Robert B. Easter  reaster@comptechnews.com ---------
- CompTechNews Message Board   http://www.comptechnews.com/ -
- CompTechServ Tech Services   http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------


how many rows? [was Re: fetching rows]

From
Arnaud Vandyck
Date:
"Robert B. Easter" wrote:
> 
> > you can't do that with a cursor, but you can use they mysql-ism called a
> > limit clause.  for example, to fetch rows 26-50 from that query, you'd
> > do:
> >
> > select * from films limit 25,26;
> >
> > or
> >
> > select * from files limit 25 offset 26;

I did know it was possible but did not know how to do, thanks (It was
not my question but I'm glad to see the solution:)

and how can I know how many rows are returned by the query?

--
Arnaud
( http://www.ressource-toi.org )


Re: how many rows? [was Re: fetching rows]

From
"Robert B. Easter"
Date:
On Friday 17 November 2000 04:01, Arnaud Vandyck wrote:
> "Robert B. Easter" wrote:
> > > you can't do that with a cursor, but you can use they mysql-ism called
> > > a limit clause.  for example, to fetch rows 26-50 from that query,
> > > you'd do:
> > >
> > > select * from films limit 25,26;
> > >
> > > or
> > >
> > > select * from files limit 25 offset 26;
>
> I did know it was possible but did not know how to do, thanks (It was
> not my question but I'm glad to see the solution:)
>
> and how can I know how many rows are returned by the query?
>

I don't know exactly.  I don't know of any way to find the total number of 
rows in a cursor.  If you really need to know, you'll have to run a count(*) 
first, then make the cursor using the same select almost.  Once you get the 
count(*), you can then use MOVE and FETCH to get the page you want.

If someone has done it a better way, I'd like to hear how.  However, 
sometimes it is possible to cache a count(*) value somewhere in the database 
so it doesn't have to be found everytime - it depends on your database and 
what the select is if you can store the count in advance somehow.

> --
> Arnaud
> ( http://www.ressource-toi.org )

-- 
-------- Robert B. Easter  reaster@comptechnews.com ---------
- CompTechNews Message Board   http://www.comptechnews.com/ -
- CompTechServ Tech Services   http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------


Re: how many rows? [was Re: fetching rows]

From
Johann Spies
Date:
On Fri, Nov 17, 2000 at 10:01:28AM +0100, Arnaud Vandyck wrote:
> "Robert B. Easter" wrote:
> > 
> > > you can't do that with a cursor, but you can use they mysql-ism called a
> > > limit clause.  for example, to fetch rows 26-50 from that query, you'd
> > > do:
> > >
> > > select * from films limit 25,26;
> > >
> > > or
> > >
> > > select * from files limit 25 offset 26;
> 
> I did know it was possible but did not know how to do, thanks (It was
> not my question but I'm glad to see the solution:)
> 
> and how can I know how many rows are returned by the query?

If you use ruby's interface to postgresql you can use num_tuples to
determine how many rows are returned by the query.

Ruby is an exiting young language somewhat like python.

Johann.
-- 
J.H. Spies - Tel. 082 782 0336 / 023 55 11 568    "A Song for the sabbath day. It is a good thing to      give thanks
untothe LORD, and to sing praises unto      thy name, O most High."   Psalms 92:1