LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 12-06-2008, 02:08 AM   #1
maradnus
Member
 
Registered: Oct 2008
Location: Yellagiri Hills
Distribution: Fedora
Posts: 87

Rep: Reputation: 15
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.
 
Old 12-06-2008, 04:49 AM   #2
jschiwal
Guru
 
Registered: Aug 2001
Location: Fargo, ND
Distribution: SuSE AMD64
Posts: 15,733

Rep: Reputation: 654Reputation: 654Reputation: 654Reputation: 654Reputation: 654Reputation: 654
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:
Code:
mysql> SELECT * FROM album ORDER BY id LIMIT 5;
+----+----------------------+
| id | name                 |
+----+----------------------+
|  6 |                      |
|  7 | Scott Bourne's Album |
| 16 | BSD Talk             |
| 21 | Valid Syntax         |
| 25 | GeekBrief.TV         |
+----+----------------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM album
    -> WHERE id > 25
    -> ORDER BY id
    -> LIMIT 5;
+----+-----------------------------------------+
| id | name                                    |
+----+-----------------------------------------+
| 26 | IBM developerWorks Interviews           |
| 27 | IBM Linux Technology Center             |
| 37 | Konqcast                                |
| 41 | Linux Basement                          |
| 45 | MuggleCast: The #1 Harry Potter Podcast |
+----+-----------------------------------------+
5 rows in set (0.00 sec)
If the data returned is ordered by a column, you can add a test on the ordered column and use LIMIT 5.
 
Old 12-06-2008, 09:54 PM   #3
graemef
Senior Member
 
Registered: Nov 2005
Location: Hanoi
Distribution: Fedora 13, Ubuntu 10.04
Posts: 2,376

Rep: Reputation: 147Reputation: 147
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).
 
Old 12-07-2008, 03:49 AM   #4
maradnus
Member
 
Registered: Oct 2008
Location: Yellagiri Hills
Distribution: Fedora
Posts: 87

Original Poster
Rep: Reputation: 15
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.
 
Old 12-07-2008, 04:47 AM   #5
graemef
Senior Member
 
Registered: Nov 2005
Location: Hanoi
Distribution: Fedora 13, Ubuntu 10.04
Posts: 2,376

Rep: Reputation: 147Reputation: 147
That shouldn't make a difference, the LIMIT and OFFSET clause come after the ORDER BY
 
Old 12-07-2008, 06:57 PM   #6
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 22,965
Blog Entries: 11

Rep: Reputation: 865Reputation: 865Reputation: 865Reputation: 865Reputation: 865Reputation: 865Reputation: 865
You didn't mention which version of Postgresql you're using,
but I think what you're after are cursors
 
Old 12-08-2008, 12:23 AM   #7
maradnus
Member
 
Registered: Oct 2008
Location: Yellagiri Hills
Distribution: Fedora
Posts: 87

Original Poster
Rep: Reputation: 15
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.
 
Old 12-08-2008, 08:57 AM   #8
sundialsvcs
Guru
 
Registered: Feb 2004
Location: SE Tennessee, USA
Distribution: Gentoo, LFS
Posts: 5,261

Rep: Reputation: 1078Reputation: 1078Reputation: 1078Reputation: 1078Reputation: 1078Reputation: 1078Reputation: 1078Reputation: 1078
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.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to compare records in two tables in seperate My Sql database using perl script chandanperl Programming 1 08-22-2008 09:33 AM
Records are getting added into mysql table automatically prabhatsoni Linux - Software 12 08-10-2007 02:20 AM
How to insert same text in one field for 10,000 records table edhan Linux - Newbie 6 06-13-2005 03:48 AM
delete records on table vickr1z Linux - Newbie 1 11-25-2004 05:27 AM
Import records to MySQL Database from a shell script chrisk5527 Linux - General 12 03-24-2004 09:49 PM


All times are GMT -5. The time now is 03:27 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration