Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Returning only limited data from database.

Posted on 2002-06-03
7
Medium Priority
?
1,494 Views
Last Modified: 2012-06-21
I am using java as programming language and PostgreSQL as my database.

Is it possible to request the database to return only a certain number of records (100 only) based on a Select Query ?


In other words, consider this example :
SELECT name, age, depname from EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.dept_id = DEPARTMENT.id;

This returns 1500 records.

Is there a way to tell the RDBMS to return only first 100 records ? Also, can i ask it to return next 100 records etc ?

The reason i need this feature is that some of my SQL Queries might return 500,000 records. I wonder if the resultset object in java will be able to handle that much data !

(One thought : What is the behaviour in LDAP. Are such features supported there.)
0
Comment
Question by:Jitu
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 5

Expert Comment

by:kelfink
ID: 7051906
Sure.

SELECT * FROM mytable  ORDER BY mytable.id
LIMIT 10 OFFSET 20;

The OFFSET is optional, and can be used to select a portion of the data set after the first row.  THe example selects the 20th through 29th rows.
0
 
LVL 1

Author Comment

by:Jitu
ID: 7053200
Kelfink that was excellent :)

Do you know which databases support LIMIT/OFFSET feature in SQL. ?

What i am trying is to implement paging for a user on a report, the data for which comes from the database. Right not i am using postgres database, but in future we may use other databases.

Are there any severe performance implications to using this.
0
 
LVL 5

Accepted Solution

by:
kelfink earned 200 total points
ID: 7054386
I don't know much about performance impacts, but I would assume that the database does have to go through most of the work of processing rows 1..19, even if you just want to get back 20-29.

All the databases I use have some form of limit. Unfortunately they all have different implementations.

On MS Sql Server, the equivalent is : SELECT TOP N ...
On Oracle, you have to use : ...WHERE ROWNUM <= N
On mySql, you use LIMIT, but it comes at the end of the statement.  Unfortunately, ANSI never defined a routine way to pick and choose portions of the result set.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 54

Expert Comment

by:nico5038
ID: 7265845

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - Answered by: kelfink  
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 1

Author Comment

by:Jitu
ID: 7266584
Thanks. Sorry for the delay in getting back.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7266760
Thanks for finalizing the Q ;-)

Nic;o)
0
 

Expert Comment

by:partner0
ID: 7426667
In various RDBMS, you only have the TOP or equivalent (SQL Server) or none (Oracle), making me think that all the DB that implement a mechanism for selecting (n to n+x) like mySQL does select TOP n+x and then discard TOP n... Which is worth even that selecting TOP n+x from a performance standpoint...

In fact, how could RDBMS reach result n+1 without reaching result n first, unless adding a search condition?

The most honest are Oracle where you explicitly add search conditions to do that... No magic from a performance standpoint...

Maybe, if you have ressource consuming search conditions, a way to enhance perf is to perform paging on client side, by passing a list of all matching ID to the client at the begining, then retreiving information by subset of IDs, saving the load of re-compute all search contidions each time you page...
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question