How to read next five records from the database table?
 

Dear Friends

Is there any way to fetch next five records from a database
table?

I am using PostgreSQL database.


Thanks In Advance.

I'm not sure what you are asking. Do you mean that if a search uses LIMIT 5, you want another search that displays the next 5 records?

This is using MySQL, but the SQL queries should be portable:
If the data returned is ordered by a column, you can add a test on the ordered column and use LIMIT 5.

Postgresql support the limit and offset clause. So:

SELECT * FROM album WHERE id > 25 ORDER BY id LIMIT 5;

would return the first five and then

SELECT * FROM album WHERE id > 25 ORDER BY id LIMIT 5 OFFSET 5;

would return the next five.

I'm not certain what would happen if data was INSERT'ed or DELETE'd between the two calls (you may get a duplicate value or miss a value).

Thank you very much for your answers...

I got the idea but the problem is I have to fetch
the records which are sorted by a text field.

Thanks in advance.

That shouldn't make a difference, the LIMIT and OFFSET clause come after the ORDER BY

You didn't mention which version of Postgresql you're using,
but I think what you're after are cursors

First of all I would like to thank you
for attending this thread...


I got the idea. It is all about using LIMIT and OFFSET where I have to change/increment OFFSET value every time when I call the function.

I am using PostgreSQL-8.1.10-1.fc6 version.


I want to make use of the above idea in the following context:

I have a UI where a table with 10 rows and two push buttons one to show next 10 records and other to show previous 10 records.

using LIMIT and OFFSET we can get next set of records.
I think I have to find a logic using LIMIT and OFFSET or CURSOR to get previous n records.

or

Is there any other way? to get the previous n records.


Thank you.

Once you have run the SELECT statement, you have a "cursor" which can be positioned anywhere within the set of rows obtained. You simply loop to pick-up the rows that you want, storing them (say) in an array or other kind of in-memory list. Use this to build and maintain your visual grid.

There are many ways to implement this, depending on the kind of application you are designing (web, for instance, or non-web...), the size of the tables you are working with, the frequency with which you expect queries to be run and the length of time you know each query will take. So, there's no single answer. But there are a few useful rules-of-thumb:

1. All "sorting" should be handled by using the ORDER BY clause in the underlying query. Only the database-engine can see the entire table, to provide you with a correctly-sorted slice.
2. Try to anticipate the search patterns that a user is likely to take. He or she should have the means to be specific, and then to page up-and-down in the result list. If the data is fairly static and if queries can be time-consuming, you can grab "a few extra page-fuls to either side," hold those in a cache (say, your session-information, for a web based app) and dole them out as the user moves around. (The most common request will be for "the next page.")
3. In non-web applications, database cursors can be held-open, but "they should not be held-open through lunchtime." Each open cursor not only consumes resources but also locks things.
4. "There are only 'rules of thumb,' no hard-and-fast bright line rules." In the end, you must use your own professional judgment, coupled with those of your peers.