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.)
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
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.
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.
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...
Business Accounts
Answer for Membership
by: kelfinkPosted on 2002-06-03 at 11:51:08ID: 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.